Aggregation & Grouping

Collapsing rows into summaries — and why WHERE and HAVING are not interchangeable

Why

Almost every business question involves aggregation: totals, averages, counts by category, top-N per group. GROUP BY is the mechanism, but the WHERE vs HAVING distinction — filtering before vs after aggregation — is where most candidates stumble. Getting it wrong produces results that look right but calculate incorrectly.

Intuition

GROUP BY collapses all rows that share the same value(s) in the specified columns into a single output row. After grouping, you can only reference columns that are either in the GROUP BY list or wrapped in an aggregate function — because individual row values no longer exist, only group-level summaries.

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. Rule of thumb: if the condition references an aggregate (COUNT, SUM, etc.), it belongs in HAVING.

Explanation

Aggregate functions and GROUP BY

SELECT department, COUNT(*) AS headcount, COUNT(manager_id) AS with_manager, -- NULL excluded from COUNT(col) AVG(salary) AS avg_salary, MAX(salary) AS max_salary, MIN(hire_date) AS earliest_hire, SUM(salary) AS payroll FROM employees GROUP BY department ORDER BY payroll DESC; -- COUNT(*) counts all rows including NULLs -- COUNT(column) counts only non-NULL values in that column -- COUNT(DISTINCT column) counts unique non-NULL values

COUNT(*) vs COUNT(column) is a classic gotcha. If a column has NULLs, the counts will differ — COUNT(column) silently skips them.

WHERE vs HAVING

-- WHERE runs before GROUP BY — filters individual rows -- HAVING runs after GROUP BY — filters groups -- Find departments with average salary > 100k AND at least 5 employees SELECT department, AVG(salary) AS avg_sal, COUNT(*) AS cnt FROM employees WHERE status = 'active' -- filter rows BEFORE grouping GROUP BY department HAVING AVG(salary) > 100000 -- filter groups AFTER aggregation AND COUNT(*) >= 5 ORDER BY avg_sal DESC; -- WRONG — can't use aggregate in WHERE: -- WHERE COUNT(*) > 5 → error: aggregate not allowed in WHERE

GROUP BY with multiple columns and ROLLUP

-- Group by multiple columns — each unique combination gets a row SELECT year, month, category, SUM(revenue) AS rev FROM sales GROUP BY year, month, category; -- ROLLUP — subtotals and grand total automatically SELECT year, month, SUM(revenue) FROM sales GROUP BY ROLLUP(year, month); -- Produces: per month, per year subtotal, and grand total -- NULL in a ROLLUP column indicates the subtotal/total row

← Previous Next →