GROUP BY, HAVING & Aggregates

Summaries per group, filtered aggregates, string and array aggregation, and subtotals with ROLLUP.

Aggregate functions collapse many rows into one value: count, sum, avg, min, max, and many more. GROUP BY computes them per group; HAVING filters the groups afterwards.

Summaries of the sample company

count(*), avg and max per department; round() keeps the average readable.

FILTER (WHERE role = 'firmware') counts only some rows of each group, alongside the total.

HAVING sum(hours) > 700 drops groups after aggregation (WHERE could not: it runs before grouping).

string_agg(name, ', ' ORDER BY name) joins the names of a group into one string.

ROLLUP (location, name) adds a subtotal row per location (name is NULL) and a grand total (both NULL).

Rules

SELECT dept_id, name, max(salary) FROM employees GROUP BY dept_id;

SELECT DISTINCT ON (dept_id) dept_id, name, salary FROM employees ORDER BY dept_id, salary DESC;

name is neither grouped nor aggregated, so PostgreSQL rejects it (MySQL used to return an arbitrary name). To get the row with the maximum, use DISTINCT ON or a window function.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping; HAVING filters groups after aggregation and can use aggregates.

How do you count active and done projects in one row?

SELECT count(*) FILTER (WHERE status = 'active') AS active, count(*) FILTER (WHERE status = 'done') AS done FROM projects;