The quarterly review is tomorrow. Lena needs total revenue by region and category. The data has 600,000 rows in a database. In SQL: seven lines, three seconds. In Excel: forty minutes and a prayer that the pivot table doesn't break.

The five aggregate functions

COUNT vs COUNT(*)

COUNT(*) counts all rows including NULLs. COUNT(column) counts only non-NULL values in that column. The difference matters when your data has missing values.

SELECT
  COUNT(*) AS TotalRows, -- includes NULLs
  COUNT(Revenue) AS RowsWithRevenue, -- excludes NULLs
  COUNT(DISTINCT Salesperson) AS UniqueSalespeople
FROM SalesFact;

GROUP BY basics

Every column in SELECT that is not inside an aggregate function must appear in GROUP BY.

SELECT Region, SUM(Revenue) AS TotalRevenue
FROM SalesFact
GROUP BY Region
ORDER BY TotalRevenue DESC;

Multiple grouping columns

SELECT Region, Category,
  COUNT(*) AS Orders,
  SUM(Revenue) AS Revenue
FROM SalesFact
GROUP BY Region, Category
ORDER BY Region, Revenue DESC;

Filtering groups with HAVING

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. This distinction is critical.

SELECT Salesperson, SUM(Revenue) AS TotalRevenue
FROM SalesFact
GROUP BY Salesperson
HAVING SUM(Revenue) > 5000 -- only salespeople with >5000 total
ORDER BY TotalRevenue DESC;
💡
Analyst tip: HAVING is the only place you can filter on an aggregate. WHERE runs before GROUP BY so the aggregate doesn't exist yet. If you need to filter on SUM(), AVG(), or COUNT(), it always goes in HAVING.
⚠️
Common mistake: Putting an aggregate filter in WHERE. WHERE SUM(Revenue) > 5000 throws an error — aggregates in WHERE is always wrong. Move it to HAVING.
🎯 Your Challenge

Find every category where the average order value exceeds £300, ordered by average order value descending. Use GROUP BY, AVG(), and HAVING together. How many categories qualify?

FAQ

Depends on the database. MySQL allows it; PostgreSQL and SQL Server do not. To be safe across all databases, repeat the aggregate expression: HAVING SUM(Revenue) > 5000 rather than HAVING TotalRevenue > 5000.
Yes, and it is common. WHERE filters rows first (reducing what gets grouped), then GROUP BY aggregates, then HAVING filters those groups. Using both together is often more efficient than filtering only in HAVING.
`