SQL Aggregates & GROUP BY

Aggregate Functions & NULL Behavior

SELECT COUNT(*), COUNT(column), COUNT(DISTINCT col), SUM(salary), AVG(salary), MIN(salary), MAX(salary)
FROM employees;

COUNT(*) counts every row including NULLs; COUNT(column) skips NULLs in that column; SUM/AVG/MIN/MAX all skip NULLs too — AVG specifically divides by the count of non-NULL values, which can silently differ from what you'd get treating NULL as 0 (AVG(COALESCE(score, 0)) if that's actually intended).

GROUP BY

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees GROUP BY department;

Rule: every non-aggregated column in SELECT must appear in GROUP BY. SELECT name, department, COUNT(*) ... GROUP BY department is invalid — name isn't grouped or aggregated, so the DB can't know which single name value to show per department group.

HAVING vs WHERE

SELECT department, COUNT(*) AS active_count
FROM employees
WHERE is_active = TRUE           -- row filter first
GROUP BY department
HAVING COUNT(*) > 3;             -- group filter after aggregation

ROLLUP / GROUPING SETS

-- ROLLUP: subtotals + grand total in one query
SELECT department, job_title, SUM(salary)
FROM employees GROUP BY ROLLUP(department, job_title);

-- GROUPING SETS: explicit control over which combinations to compute
SELECT department, job_title, SUM(salary)
FROM employees
GROUP BY GROUPING SETS ((department, job_title), (department), ());

Common Patterns

-- Percentage of total (window function combined with GROUP BY)
SELECT department, COUNT(*) AS dept_count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct
FROM employees GROUP BY department;

-- Users with exactly 1 order — not repeated buyers
SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) = 1;

-- Conditional counting (manual pivot — see [[SQL Pivoting]])
SELECT
  SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

Interview Drills

1. Why does SELECT name, department, COUNT(*) FROM employees GROUP BY department fail? — name isn't in GROUP BY and isn't wrapped in an aggregate — the database has no defined way to pick a single name to display per department group, since each group can contain many different names.
2. Why use HAVING COUNT(*) > 3 instead of WHERE COUNT(*) > 3? — WHERE filters individual rows before grouping/aggregation happens, so no COUNT(*) value exists yet at that stage — HAVING is specifically the post-aggregation filter clause.

Cross-links

[[SQL Basics & Query Fundamentals]] · [[SQL Window Functions]] · [[SQL Pivoting]]