GROUP BY and HAVING

GROUP BY sorts the rows into groups that share the same value, and then the aggregates run once per group. You get one result row per group instead of one for the whole table:

1SELECT CategoryId,2       COUNT(*)   AS Products,3       SUM(Stock) AS Units,4       MAX(Price) AS Dearest5FROM dbo.Products6GROUP BY CategoryId7ORDER BY CategoryId;

CategoryId  Products  Units  Dearest
----------  --------  -----  -------
         1         3    117    89.00
         2         2    520     7.20
         3         2    580    42.00
         4         2    150    11.90
         5         2     55    54.00
         6         1      0     6.80

SELECT CategoryId, COUNT(*)
FROM dbo.Products
GROUP BY CategoryId;

The rule for the SELECT list

After grouping, each result row stands for a whole group. So every column in SELECT must be either in the GROUP BY (it has one value per group) or inside an aggregate (it is summarised). Anything else is an error:

1SELECT CategoryId, Name, COUNT(*) AS Products2FROM dbo.Products3GROUP BY CategoryId;

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

To show the category name, join Categories and group by the name as well. Grouping by both CategoryId and Name makes the same groups, since each id has one name:

1SELECT c.CategoryId, c.Name,2       COUNT(*)     AS Products,3       AVG(p.Price) AS AvgPrice4FROM dbo.Products AS p5JOIN dbo.Categories AS c ON c.CategoryId = p.CategoryId6GROUP BY c.CategoryId, c.Name7ORDER BY Products DESC, c.Name;

CategoryId  Name      Products   AvgPrice
----------  --------  --------  ---------
         1  Tools            3  39.083333
         2  Building         2   5.650000
         4  Fixings          2   8.200000
         5  Paint            2  33.250000
         3  Timber           2  24.400000
         6  Safety           1   6.800000

The order SQL Server runs a query in

You write SELECT first, but SQL Server works through the clauses in a different order. This logical order explains most of the rules in this lesson:

So WHERE cannot use an aggregate (the groups do not exist yet), and HAVING cannot use a SELECT alias (it has not been given yet). ORDER BY runs last, so it can use either.

WHERE filters rows, HAVING filters groups

Which customers have spent more than 100 on shipped orders? The status test is about single rows, so it goes in WHERE. The spending test is about a group total, so it goes in HAVING:

1SELECT o.CustomerId,2       COUNT(DISTINCT o.OrderId)       AS Orders,3       SUM(oi.Quantity * oi.UnitPrice) AS Spent4FROM dbo.Orders AS o5JOIN dbo.OrderItems AS oi ON oi.OrderId = o.OrderId6WHERE o.Status = 'Shipped'7GROUP BY o.CustomerId8HAVING SUM(oi.Quantity * oi.UnitPrice) > 1009ORDER BY Spent DESC;

CustomerId  Orders   Spent
----------  ------  ------
         4       2  723.50
         6       2  211.85
         1       2  150.80
         2       1  113.00

Putting the aggregate test in WHERE fails, because WHERE runs before there are any groups to add up:

1SELECT CategoryId, COUNT(*) AS Products2FROM dbo.Products3WHERE COUNT(*) >= 24GROUP BY CategoryId;

Msg 147, Level 15, State 1, Line 3
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

And HAVING cannot use the alias from SELECT, so repeat the expression:

1SELECT CategoryId, COUNT(*) AS Products2FROM dbo.Products3GROUP BY CategoryId4HAVING Products >= 2;

Msg 207, Level 16, State 1, Line 4
Invalid column name 'Products'.

Grouping by an expression

You can group by anything you can calculate. Orders per month, using the first day of each month as the group:

1SELECT DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS Month,2       COUNT(*) AS Orders3FROM dbo.Orders4GROUP BY DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)5ORDER BY Month;

Month       Orders
----------  ------
2024-02-01       2
2024-03-01       2
2024-04-01       2
2024-05-01       2
2024-06-01       2

January has no orders, so it has no rows to group and does not appear. A group only exists if at least one row falls into it. The recursive CTE lesson shows how to list every month, including the empty ones.

Counting empty groups correctly

To include categories with no products you need a LEFT JOIN. But then which COUNT you use matters:

1SELECT c.Name,2       COUNT(*)           AS CountStar,3       COUNT(p.ProductId) AS CountProducts4FROM dbo.Categories AS c5LEFT JOIN dbo.Products AS p ON p.CategoryId = c.CategoryId6GROUP BY c.Name7ORDER BY c.Name;

Warning: Null value is eliminated by an aggregate or other SET operation.

Name      CountStar  CountProducts
--------  ---------  -------------
Building          2              2
Fixings           2              2
Paint             2              2
Plumbing          1              0
Safety            1              1
Timber            2              2
Tools             3              3

Plumbing has no products, but the LEFT JOIN still gives it one row, with NULLs in every Products column. COUNT(*) counts that row and says 1. COUNT(p.ProductId) skips the NULL and says 0, which is the right answer.

Groups of groups

GROUP BY with several columns makes one group for each combination that occurs. Orders by customer and status:

1SELECT CustomerId, Status, COUNT(*) AS Orders2FROM dbo.Orders3GROUP BY CustomerId, Status4ORDER BY CustomerId, Status;

CustomerId  Status     Orders
----------  ---------  ------
         1  Shipped         2
         2  Pending         1
         2  Shipped         1
         3  Cancelled       1
         4  Shipped         2
         6  Shipped         2
         8  Pending         1

Subtotals with ROLLUP

GROUP BY ROLLUP (a) adds an extra row with the grand total. In that row the grouped column is NULL, and GROUPING(a) returns 1 to tell a total row from a real NULL:

1SELECT CASE WHEN GROUPING(Status) = 1 THEN 'All orders' ELSE Status END AS Status,2       COUNT(*) AS Orders3FROM dbo.Orders4GROUP BY ROLLUP (Status);

Status      Orders
----------  ------
Cancelled        1
Pending          2
Shipped          7
All orders      10

1SELECT p.Name,2       COUNT(*)                        AS Lines,3       SUM(oi.Quantity)                AS QtySold,4       SUM(oi.Quantity * oi.UnitPrice) AS Value5FROM dbo.OrderItems AS oi6JOIN dbo.Products AS p ON p.ProductId = oi.ProductId7GROUP BY p.ProductId, p.Name8HAVING COUNT(*) > 19ORDER BY Value DESC;

Name                 Lines  QtySold   Value
-------------------  -----  -------  ------
Timber 2x4 2.4 m         2       52  353.60
Plywood sheet 18 mm      2        8  336.00
Cement 25 kg             2       30  212.00
Wood screws (200)        2        7   83.30
Claw hammer              2        2   37.00
Tape measure 8 m         2        3   29.25