SQL - Group By

SQL GROUP BY aggregates (consolidates and calculates) column values into a single record value. GROUP BY requires a list of table columns on which to run the calculations. At first, this behavior will resemble the SELECT DISTINCT command we toyed with earlier.

SQL Group By:

USE mydatabase;

SELECT customer
FROM orders
GROUP BY customer;

SQL Results:

customer

A+Maintenance

Gerald Garner

Tia

Here, SQL has consolidated like values and returned those that are unique. In this case, we have actually duplicated the behavior of SELECT DISTINCT, but you have also seen firsthand how GROUP BY accepts a table column as a list and consolidates like customer values.
To unleash the true power of GROUP BY, it is necessary to include at least one mathematical (aggregate) function, and to do so we will utilize the SUM() function to calculate how many total items have been purchased by each of our customers.

SQL Code:

USE mydatabase;

SELECT customer, SUM(quantity) AS "Total Items"
FROM orders
GROUP BY customer;

SQL Results:

customer	Total Items
A+Maintenance	14
Gerald Garner	5
Tia	23

With the addition of the aggregate SUM() function, we've let SQL calculate how many products have been ordered by each customer and returned them for viewing with a single query statement.
Taking a look at another example, we can also figure out how many of each product was ordered with the use of a single query statement.

SQL Code:

USE mydatabase;

SELECT product, SUM(quantity) AS "Total Items"
FROM orders
GROUP BY product;

SQL Results:

product	Total Items
19" LCD Screen	10
Hanging Files	25
HP Printer	4
Stapler	3

GROUP BY would also be a great way to calculate how much total cash of our customers has spent. Let's take a look at what that query may look like.

SQL Code:

USE mydatabase;

SELECT customer,
  SUM((orders.quantity * inventory.price)) AS "COST"
FROM orders
JOIN inventory
ON orders.product = inventory.product
GROUP BY customer;

SQL Results:

product	COST
A+Maintenance	209.86
Gerals Garner	899.95
Tia	1448.77

SQL - Grouping By Multiple Columns

Like the ORDER BY clause, GROUP BY can accept a list of table columns on which to group by.

SQL Code:

USE mydatabase;

SELECT day_of_order,
  product,
  SUM(quantity) as "Total"
FROM orders
GROUP BY day_of_order,product
ORDER BY day_of_order;

SQL Results:

day_of_order	product	Total
2008-07-25 00:00:00.000	19" LCD Screen	5
2008-07-25 00:00:00.000	HP Printer	4
2008-08-01 00:00:00.000	Hanging Files	11
2008-08-01 00:00:00.000	Stapler	3
2008-08-15 00:00:00.000	19" LCD Screen	5
2008-08-16 00:00:00.000	Hanging Files	14

This query will group together and sum the total number of products purchased on any given date, regardless of what customer has purchased the item. It's a very useful query to keep in mind.

SQL - Having

The SQL HAVING clause is "like a WHERE clause for aggregated data." It's used with conditional statements, just like WHERE, to filter results. One thing to note is that any column name appearing in the HAVING clause must also appear in the GROUP BY clause.

SQL Having:

USE mydatabase;

SELECT day_of_order,
  product,
  SUM(quantity) as "Total"
FROM orders
GROUP BY day_of_order,product,quantity
HAVING quantity > 7
ORDER BY day_of_order;

SQL Results:

day_of_order	product	Total
2008-08-01 00:00:00.000	Hanging Files	11
2008-08-16 00:00:00.000	Hanging Files	14

The quantity column is now considered aggregated in SQL terms, because its values have been summed together using the SUM() function. In the example above, HAVING acts as the WHERE clause for aggregate values, filtering out results that do not meet the condition (quantity > 7).

Posted in: SQL