Aggregation & Grouping

Aggregation goes far beyond GROUP BY ... HAVING. This lesson covers GROUPING SETS, ROLLUP, CUBE, the FILTER clause, and conditional aggregation — tools that produce sophisticated summaries in a single query.

---

GROUP BY Refresher

SELECT
  department,
  COUNT(*) AS employee_count,
  AVG(salary) AS avg_salary,
  MAX(salary) AS max_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

Rules

• Every column in SELECT must be either aggregated or in the GROUP BY list.
• WHERE filters rows before grouping; HAVING filters after grouping.

---

HAVING

Filter on aggregated values:

-- Departments with more than 10 employees and average salary above 60,000
SELECT
  department,
  COUNT(*) AS headcount,
  AVG(salary)::numeric(10,2) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 10
   AND AVG(salary) > 60000;

Common mistake: Using WHERE instead of HAVING for aggregate conditions. WHERE COUNT(*) > 10 is a syntax error.

---

GROUPING SETS

Compute multiple levels of aggregation in a single query. Each grouping set defines one grouping: