# 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 November 16, 2026

By Gregor Dzierzon

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.

```sql
SELECT SUM(Population) AS total_population
FROM country
WHERE Continent = 'Europe';
```

This approach requires a separate query for each continent.

```sql
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.

```sql
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.

```sql
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.

```sql
-- 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.

```sql
-- this query returns 13 rows
SELECT SUM(Population) AS total_population
    , Region
FROM country
GROUP BY Region
HAVING SUM(Population) > 100000000;
```

In other words:

- `WHERE` filters rows, before they are grouped.
- `HAVING` filters 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) and `total_in_stock` (their `units_in_stock` added 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.

## Cheat sheets

- [Grouping Aggregations](https://www.ask-codey.com/cheat-sheets/aggregate-functions-grouping-aggregations-cheat-sheet)

Source: https://www.ask-codey.com/lessons/aggregate-functions-grouping-aggregations
