Grouping and aggregate functions
Reports need totals, counts and averages: sales per month, orders per customer, the average rating per product. This lesson explains aggregate functions, GROUP BY and HAVING, and the order in which they run.
- StartFROMStart with all order rows
- Step 2WHERERemove rows you do not want, such as cancelled orders
- Step 3GROUP BYPut the remaining rows into groups, one per customer
- Step 4AggregatesCOUNT, SUM, AVG, MIN, MAX calculated per group
- Step 5HAVINGKeep only groups that pass a condition, such as SUM > 5000
- EndORDER BYSort the summary rows
The idea in plain words
Aggregate functions turn many rows into one value: COUNT counts rows, SUM adds, AVG averages, MIN and MAX find the extremes. Without GROUP BY they summarise the whole table. With GROUP BY they produce one summary row per group.
WHERE filters rows before grouping; HAVING filters groups after the aggregates are calculated. Every column in the SELECT list must either be in GROUP BY or inside an aggregate.
Example
-- Totals for the whole table
SELECT COUNT(*) AS Orders, SUM(Total) AS Revenue, AVG(Total) AS AverageOrder
FROM Orders;
-- One row per customer, only big spenders
SELECT c.Name, COUNT(*) AS Orders, SUM(o.Total) AS Spent
FROM Orders AS o
JOIN Customers AS c ON c.CustomerId = o.CustomerId
WHERE o.Status <> N'Cancelled'
GROUP BY c.Name
HAVING SUM(o.Total) > 5000
ORDER BY Spent DESC;
-- Sales per month
SELECT YEAR(OrderedAt) AS [Year], MONTH(OrderedAt) AS [Month], SUM(Total) AS Revenue
FROM Orders
GROUP BY YEAR(OrderedAt), MONTH(OrderedAt)
ORDER BY [Year], [Month];Common mistakes
- Selecting a column that is neither grouped nor aggregated, which is an error in SQL Server.
- Using WHERE for a condition on an aggregate; that belongs in HAVING.
- Forgetting that COUNT(column) skips NULLs while COUNT(*) counts every row.
- Grouping by a display name that is not unique; group by the id and the name together.
Interview question
What is the difference between WHERE and HAVING?
WHERE filters individual rows before they are grouped and cannot use aggregate functions. HAVING filters the groups produced by GROUP BY after aggregates are calculated, so it can use conditions like HAVING COUNT(*) > 5. Use WHERE whenever possible because it reduces the number of rows that need to be grouped.
Want the full details? Read the official Microsoft documentation for this topic.