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

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

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:

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

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?

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?

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

Cheat sheets