# 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 |
Tizag |
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 |
Tizag | 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 |
Tizag | 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).
# Found Something Wrong in this Lesson?Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time! |