Grouping Aggregations
How GROUP BY returns one aggregate row per group, and how HAVING filters those groups where WHERE filters rows.
Journeyman · SQL · MySQL · Updated
Aggregation functions by themselves can only be used to aggregate the entire result set. For example, if you want to calculate the total population of a continent, you would need to add a where clause to specify the continent.
SELECT SUM(Population) AS total_population
FROM country
WHERE Continent = 'Europe';
This approach requires a separate query for each continent.
SELECT SUM(Population) AS total_population
FROM country
WHERE Continent = 'Asia';
Writing a single query that returns 1 row for each continent is also possible. However, you must provide instructions on grouping the rows to calculate the aggregations correctly.
GROUP BY
The GROUP BY clause is a section of the SELECT statement that is used to provide the grouping instruction.
SELECT SUM(Population) AS total_population
, Continent
FROM country
GROUP BY Continent;
NOTE: If a column that is NOT included in an aggregation is in the SELECT statement IT MUST be added to the GROUP BY clause. For example, in the previous example the Continent column must be added to the GROUP BY clause.
A SELECT statement can be grouped by multiple columns.
SELECT SUM(Population) AS total_population
, Region
, Continent
FROM country
GROUP BY Region
, Continent;
Using Groups to Filter
The WHERE clause is a statement that allows you to filter out rows based on the row's individual value. You may want to filter your data after the rows are grouped and the aggregates calculated.
For instance, if you want to create a report that returns all regions whose population is over 100,000,000 - the following query would return the incorrect results.
-- this query returns 7 rows
SELECT SUM(Population) AS total_population
, Region
FROM country
WHERE Population > 100000000
GROUP BY Region;
The above query will only include countries in each region whose population is over 100,000,000 people.
If we want to include all countries in each region whose total region population is over 100,000,000, the query needs to be re-written as follows.
-- this query returns 13 rows
SELECT SUM(Population) AS total_population
, Region
FROM country
GROUP BY Region
HAVING SUM(Population) > 100000000;
In other words:
WHEREfilters rows, before they are grouped.HAVINGfilters groups, after the aggregates are calculated.
Do it: count the stock in each category
The warehouse wants one row for each category: how many products it has, and how many units of them are in stock altogether.
The products table (the Tables tab shows the SQL that makes it) has these rows:
| product_name | category_id | units_in_stock |
|---|---|---|
| Chai | 1 | 39 |
| Chang | 1 | 17 |
| Aniseed Syrup | 2 | 13 |
| Chef Anton's Cajun Seasoning | 2 | 53 |
| Grandma's Boysenberry Spread | 2 | 120 |
| Ipoh Coffee | 1 | 17 |
| Pavlova | 3 | 29 |
| Teatime Chocolate Biscuits | 3 | 25 |
Put into one pile per category, they make:
| category_id | Its products | product_count | total_in_stock |
|---|---|---|---|
| 1 | Chai, Chang, Ipoh Coffee | 3 | 39 + 17 + 17 = 73 |
| 2 | Aniseed Syrup, Chef Anton's Cajun Seasoning, Grandma's Boysenberry Spread | 3 | 13 + 53 + 120 = 186 |
| 3 | Pavlova, Teatime Chocolate Biscuits | 2 | 29 + 25 = 54 |
So your query should give exactly these rows, in this order:
| category_id | product_count | total_in_stock |
|---|---|---|
| 1 | 3 | 73 |
| 2 | 3 | 186 |
| 3 | 2 | 54 |
Your job: write one SELECT query that gives one row per category.
- Show three columns, in this order:
category_id,product_count(how many products in that category) andtotal_in_stock(theirunits_in_stockadded up). - Make one row for each category.
- Sort the rows by
category_id, smallest first.
Practiced live on the lesson page: write the SQL right there and check it.