GROUP BY, HAVING & GROUPING SETS

GROUP BY

SELECT department, count(*) AS total, round(avg(cgpa),2) AS avg_cgpa
FROM students
GROUP BY department
ORDER BY total DESC;

SELECT department, extract(year FROM admitted_on) AS batch, count(*)
FROM students
GROUP BY department, batch      -- output column name works in GROUP BY
ORDER BY 1, 2;

Postgres allows GROUP BY 1, 2 (positional) and grouping by an output alias, which MySQL does not accept in all cases.

Grouping rules

Every column in SELECT must either appear in GROUP BY or be inside an aggregate. Postgres enforces this always — there is no equivalent of MySQL's older lenient behaviour.

One useful exception: if you group by a table's primary key, Postgres knows every other column of that table is functionally dependent and lets you select them:

SELECT s.id, s.name_en, count(e.*) AS courses
FROM students s LEFT JOIN enrollments e ON e.student_id = s.id
GROUP BY s.id;     -- name_en is allowed because id is the primary key

WHERE vs HAVING

	WHERE	HAVING
Runs	Before grouping	After grouping
Filters	Rows	Groups
Aggregates	Not allowed	Allowed

SELECT department, count(*) AS total, avg(cgpa) AS avg_cgpa
FROM students
WHERE admitted_on >= '2023-01-01'
GROUP BY department
HAVING count(*) >= 5 AND avg(cgpa) > 3.0
ORDER BY avg_cgpa DESC;

Always filter in WHERE when you can — fewer rows entering the grouping step is always faster.

ROLLUP, CUBE and GROUPING SETS

-- subtotals per department, then a grand total
SELECT department, extract(year FROM admitted_on) AS batch, count(*)
FROM students
GROUP BY ROLLUP (department, batch);

-- every combination of the two columns
SELECT department, batch, count(*)
FROM students
GROUP BY CUBE (department, batch);

-- exactly the combinations you list
SELECT department, batch, count(*)
FROM students
GROUP BY GROUPING SETS ((department), (batch), ());

Subtotal rows carry NULL in the rolled-up columns. Use GROUPING() to distinguish a real NULL from a subtotal marker:

SELECT
  CASE WHEN grouping(department) = 1 THEN 'ALL' ELSE department END AS department,
  count(*)
FROM students
GROUP BY ROLLUP (department);

These three clauses let one query produce what would otherwise be three or four separate queries stitched together with UNION ALL.

Reporting patterns

-- duplicate detection
SELECT email, count(*) FROM students
GROUP BY email HAVING count(*) > 1;

-- top 3 departments by average CGPA
SELECT department, round(avg(cgpa),2) AS avg_cgpa
FROM students GROUP BY department
ORDER BY avg_cgpa DESC LIMIT 3;

-- monthly cohort report
SELECT date_trunc('month', created_at) AS month, count(*)
FROM students GROUP BY 1 ORDER BY 1;