# Grouping Aggregations

GROUP BY splits the rows into groups and runs the aggregates once per group. Every column in the SELECT that isn't aggregated must be in the GROUP BY.

Journeyman · T-SQL · SQL · Updated November 3, 2026

## After this lesson you can

- Aggregate per group with GROUP BY, by one column or several
- Explain why every non-aggregated column in the SELECT must be in the GROUP BY

## Key takeaways

- `GROUP BY` sorts the rows into groups and runs the aggregates once per group, so each group becomes one row.
- Every column in the `SELECT` must be aggregated or in the `GROUP BY`. A group is one row, and SQL Server won't guess which value to show.
- Grouping by several columns, such as `GROUP BY Country, City`, makes one group for each combination.
- In a query, `GROUP BY` goes after `WHERE` and before `ORDER BY`.
- To group by an expression, repeat it, as in `GROUP BY YEAR(OrderDate)`: the `SELECT`'s alias doesn't exist yet when the `GROUP BY` runs.

An aggregate on its own answers one question about all the rows. To ask how many products are in category 1, you'd filter with `WHERE CategoryId = 1`. For category 2, another query. For all eight categories, eight queries.

**`GROUP BY`** answers them all at once.

## One row per group

```sql
SELECT CategoryId
    , COUNT(*) AS Products
    , AVG(UnitPrice) AS AveragePrice
FROM Products
GROUP BY CategoryId
ORDER BY CategoryId;
```

| CategoryId | Products | AveragePrice |
|---|---|---|
| 1 | 12 | 37.9791 |
| 2 | 12 | 23.0625 |
| 3 | 13 | 25.16 |
| 4 | 10 | 28.73 |
| 5 | 7 | 20.25 |
| 6 | 6 | 54.0066 |
| 7 | 5 | 32.37 |
| 8 | 12 | 20.6825 |

`GROUP BY CategoryId` sorts the products into one pile per category. Then `COUNT` and `AVG` run **once per pile**, and each pile becomes one row.

`GROUP BY` goes after `WHERE` and before `ORDER BY`. (Category names arrive in the next chapter, *Joins in T-SQL*.)

## The GROUP BY rule

Every column in the `SELECT` must either be **aggregated** or be **in the `GROUP BY`**. Break the rule, and SQL Server refuses:

```sql
SELECT CategoryId, ProductName, COUNT(*) AS Products
FROM Products
GROUP BY CategoryId;
```

```text
Msg 8120, Level 16, State 1
Column 'Products.ProductName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
```

It makes sense: category 1 is **one** row in the result, but it has twelve products. Which `ProductName` should that row show? SQL Server won't guess. Either aggregate it (`MAX(ProductName)`, `COUNT(ProductName)`) or group by it too.

## Grouping by more than one column

List several columns, and there's one group for each combination. How many customers in each city of each country?

```sql
SELECT Country, City, COUNT(*) AS Customers
FROM Customers
GROUP BY Country, City
ORDER BY Customers DESC, Country, City;
```

| Country | City | Customers |
|---|---|---|
| UK | London | 6 |
| Mexico | México D.F. | 5 |
| Brazil | Sao Paulo | 4 |
| Argentina | Buenos Aires | 3 |
| Brazil | Rio de Janeiro | 3 |
| Spain | Madrid | 3 |

(The first 6 of 69 rows.)

## Grouping by a calculation

You can group by an expression, such as the year of a date. How many orders in each year?

```sql
SELECT YEAR(OrderDate) AS OrderYear, COUNT(*) AS Orders
FROM Orders
GROUP BY YEAR(OrderDate)
ORDER BY OrderYear;
```

| OrderYear | Orders |
|---|---|
| 2016 | 152 |
| 2017 | 408 |
| 2018 | 270 |

The `GROUP BY` repeats the expression, `YEAR(OrderDate)`: SQL Server reads the `GROUP BY` before the `SELECT`, so the alias `OrderYear` doesn't exist there yet. (`ORDER BY` runs last, so it can use the alias.)

## Check yourself

2 questions to try on the lesson page.

## So what

`GROUP BY` turns one aggregate into a whole report: a row per category, per city, per year. Group by what you want a row for, aggregate everything else, and the report writes itself.

## Key terms

- [GROUP BY](https://www.ask-codey.com/glossary/group-by)

## Cheat sheets

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

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