Aggregation, GROUP BY and HAVING

GROUP BY collapses rows into groups and computes one value per group. The mechanics are simple; the interview value is in four specific things — the WHERE/HAVING split, what COUNT actually counts, conditional aggregation, and computing a median without a built-in.

The functions

COUNT(*) versus COUNT(column) is asked constantly, and the difference matters:

SELECT
    COUNT(*)             AS all_employees,   -- 10
    COUNT(commission)    AS with_commission, -- 4 — six are NULL
    AVG(commission)      AS avg_commission   -- sum / 4, NOT sum / 10
FROM employees;

If the intended average treats a missing commission as zero, that is AVG(COALESCE(commission, 0)) — a different number, and being able to say which one the question wants is the point. See NULL handling.

GROUP BY and the SELECT rule

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

Every non-aggregated column in SELECT must appear in GROUP BY. The reason is mechanical: after grouping, a column not in the grouping key has many values per group and there is no rule for picking one.

MySQL historically allowed it and returned an arbitrary value — ONLY_FULL_GROUP_BY is on by default in modern versions and rejects it, matching every other engine.

WHERE versus HAVING

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'    -- filters ROWS, before grouping
GROUP BY department
HAVING COUNT(*) > 5                -- filters GROUPS, after aggregating
ORDER BY avg_salary DESC;

WHERE cannot contain an aggregate, because it runs before the aggregates exist. HAVING can, because it runs after. The corollary interviewers like: **a condition with no aggregate should go in WHERE, not HAVING** — it is logically the same result but filters rows earlier, so fewer rows are grouped.

Conditional aggregation

The most useful single trick in reporting SQL: put a CASE inside the aggregate to count or sum only some rows, producing several answers in one pass.

SELECT
    department,
    COUNT(*)                                            AS total,
    COUNT(CASE WHEN salary > 100000 THEN 1 END)         AS high_earners,
    SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END)       AS women,
    AVG(CASE WHEN is_active THEN salary END)            AS avg_active_salary
FROM employees
GROUP BY department;

COUNT ignores NULL, so a CASE with no ELSE yields NULL for non-matching rows and they are simply not counted — which is why COUNT(CASE WHEN … THEN 1 END) needs no ELSE 0 while SUM does. This is also how pivoting works.

String aggregation

Collapsing a group's values into one delimited string. The function name differs per engine, which is itself a fair interview question:

-- PostgreSQL
SELECT department, STRING_AGG(first_name, ', ' ORDER BY first_name) FROM employees GROUP BY department;

-- MySQL
SELECT department, GROUP_CONCAT(first_name ORDER BY first_name SEPARATOR ', ') FROM employees GROUP BY department;

-- SQL Server 2017+
SELECT department, STRING_AGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name) FROM employees GROUP BY department;

-- Oracle
SELECT department, LISTAGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name) FROM employees GROUP BY department;

MySQL's GROUP_CONCAT silently truncates at group_concat_max_len (1024 bytes by default) — worth mentioning.

Median, mode and percentiles

There is no portable MEDIAN(), which is exactly why it gets asked. Two approaches:

-- PostgreSQL / Oracle: the ordered-set aggregate
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median FROM employees;

-- Portable: number the rows from both ends and take the middle one or two
WITH ranked AS (
    SELECT salary,
           ROW_NUMBER() OVER (ORDER BY salary)      AS asc_pos,
           ROW_NUMBER() OVER (ORDER BY salary DESC) AS desc_pos,
           COUNT(*) OVER ()                          AS n
    FROM employees
)
SELECT AVG(salary) AS median
FROM ranked
WHERE asc_pos IN ((n + 1) / 2, (n + 2) / 2);

The trick in the portable version: for an odd count both expressions pick the same row, and for an even count they pick the two middle rows, which AVG then averages. Mode is simpler — group by the value, order by the count descending, take the top.

GROUPING SETS, ROLLUP and CUBE

Subtotals without a UNION ALL of several queries:

SELECT department, job_title, SUM(salary)
FROM employees
GROUP BY ROLLUP (department, job_title);
-- per department+title, per department, and a grand total

Worth naming even if you do not write it — it signals reporting experience.

Mistakes that cost the interview

• `COUNT(column)` when `COUNT(*)` was meant, quietly undercounting where NULLs exist.
• Non-aggregated columns missing from `GROUP BY`.
• Aggregates in `WHERE`. They belong in HAVING.
• Non-aggregate filters in `HAVING`, which works but scans more rows than needed.
• Forgetting `AVG` skips NULLs, so the denominator is not the row count.
• Expecting groups with zero rows to appear. GROUP BY can only produce groups that

have rows — for "departments with no employees" you need a LEFT JOIN from the departments table.

Practise these on the SQL sheet.