GROUP BY & HAVING: Per-Category Aggregates

From one summary to one-per-group

A scalar aggregate gives one value for the whole table. GROUP BY partitions the rows into groups by the value(s) of one or more columns and computes the aggregate once per group — a *vector aggregate*:

SELECT lead_emp_id, COUNT(*) AS projects_led
FROM projects
GROUP BY lead_emp_id;

This returns one row per lead_emp_id, each with that manager’s project count.

The SELECT-list rule (the #1 GROUP BY error)

Once rows collapse into groups, the only single-valued things available per group are the grouping columns themselves and aggregate functions. So:

> Every column in the SELECT list must either appear in the GROUP BY *or* be inside an aggregate function.

SELECT semester, room, COUNT(*) FROM sections GROUP BY semester is an error: room is neither grouped nor aggregated, and a semester can span many rooms — SQL can’t pick one. Either add room to the GROUP BY or wrap it in an aggregate.

WHERE vs HAVING — different jobs, different times

This is the contrast the exam loves. Both filter, but at different stages of processing:

- WHERE filters individual rows *before* grouping. It cannot reference an aggregate (aggregation hasn’t happened yet). - HAVING filters groups *after* grouping. It can (and usually does) reference an aggregate.

Processing order:  FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

So WHERE COUNT(*) > 2 is illegal; the group-level condition must be HAVING COUNT(*) > 2.

Worked example: rows then groups

“List each employee who has two or more recorded skills, with the count”:

SELECT emp_id, COUNT(*) AS n_skills
FROM employee_skills
GROUP BY emp_id
HAVING COUNT(*) >= 2;

GROUP BY emp_id makes one group per employee; COUNT(*) counts each one’s skill rows; HAVING COUNT(*) >= 2 keeps only the groups whose count reaches two. If we *also* wanted to ignore a particular skill before counting, that pre-grouping filter would go in a WHERE clause — rows first (WHERE), groups second (HAVING).