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:

SELECT
  region,
  category,
  SUM(sales) AS total_sales
FROM product_sales
GROUP BY GROUPING SETS (
  (region, category),   -- per region and category
  (region),             -- per region (category is NULL)
  (category),           -- per category (region is NULL)
  ()                    -- grand total (both NULL)
)
ORDER BY region NULLS LAST, category NULLS LAST;

region	category	total_sales
EU	Electronics	50000
EU	Books	12000
EU	NULL	62000
US	Electronics	75000
US	Books	18000
US	NULL	93000
NULL	Electronics	125000
NULL	Books	30000
NULL	NULL	155000

---

ROLLUP

A shorthand for GROUPING SETS that creates a hierarchy from left to right, including the grand total:

-- ROLLUP(a, b, c) = GROUPING SETS ((a,b,c), (a,b), (a), ())
SELECT
  year,
  quarter,
  month,
  SUM(revenue) AS total_revenue
FROM monthly_revenue
GROUP BY ROLLUP (year, quarter, month)
ORDER BY year, quarter, month;

This produces:

• year + quarter + month (detail)
• year + quarter (subtotal)
• year (subtotal)
• grand total

---

CUBE

Generates all possible combinations of the specified columns:

-- CUBE(a, b) = GROUPING SETS ((a,b), (a), (b), ())
SELECT
  region,
  product_line,
  SUM(units_sold) AS units,
  SUM(revenue) AS revenue
FROM sales
GROUP BY CUBE (region, product_line)
ORDER BY region NULLS LAST, product_line NULLS LAST;

Tip: CUBE with N columns produces 2^N grouping sets. For 3 columns, that is 8 grouping sets.

---

GROUPING() Function

Distinguish between a real NULL value and a NULL introduced by GROUPING SETS/ROLLUP/CUBE: