Chapter 14: Grouping and Aggregating

Aggregations turn many rows into summarized results; grouping decides which rows belong together. This chapter explains GROUP BY and HAVING, introduces core aggregate functions, and shows advanced multi-level summaries with ROLLUP, CUBE, and GROUPING SETS.

GROUP BY, HAVING

A basic grouped query follows this pattern:

SELECT {group_columns …}, {aggregates …}
FROM {table}
[WHERE {row filters …}]
GROUP BY {group_columns …}
[HAVING {aggregate filters …}]
[ORDER BY {columns …}];

Single and multi-column GROUP BY

-- Orders per customer
SELECT o.customer_id, COUNT(*) AS order_count
FROM orders AS o
GROUP BY o.customer_id;

-- Revenue per customer and year
SELECT o.customer_id,
       EXTRACT(YEAR FROM o.order_date) AS yr,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY o.customer_id, EXTRACT(YEAR FROM o.order_date);

Grouping by expressions is allowed; you do not need to repeat the expression in an alias in every engine. Use the same expression in GROUP BY that you select.

WHERE vs HAVING

Use WHERE to filter input rows before grouping. Use HAVING to filter groups after aggregation.

-- Count only OPEN orders per customer; drop customers with zero OPEN orders
SELECT o.customer_id, COUNT(*) AS open_orders
FROM orders AS o
WHERE o.status = 'OPEN'
GROUP BY o.customer_id
HAVING COUNT(*) > 0;

Handling NULL in groups

Rows where the grouping expression evaluates to NULL are grouped together in a single NULL group. Aggregate functions ignore NULL values except COUNT(*) which counts rows regardless of NULLs.

-- Count orders per coupon; NULL coupon rows form one group
SELECT o.coupon_code, COUNT(*) AS n
FROM orders AS o
GROUP BY o.coupon_code;

Aliasing in GROUP BY and ORDER BY

-- Most engines allow using select-list aliases in ORDER BY
SELECT c.customer_id AS id, COUNT(*) AS orders
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
GROUP BY c.customer_id
ORDER BY orders DESC;

-- Some engines allow ordinal positions in ORDER BY; avoid for readability
-- ORDER BY 2 DESC;

COUNT, SUM, AVG, MIN, MAX, DISTINCT aggregates

These standard aggregates work across engines.

COUNT variants

-- Count rows regardless of NULLs
SELECT COUNT(*) FROM orders;

-- Count non-NULL values in a column
SELECT COUNT(ship_date) FROM orders;

-- Count distinct customers who ordered this year
SELECT COUNT(DISTINCT customer_id)
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2025;

SUM and AVG

-- Revenue per product
SELECT oi.product_id,
       SUM(oi.quantity * oi.unit_price) AS revenue,
       AVG(oi.unit_price) AS avg_price
FROM order_items AS oi
GROUP BY oi.product_id;

AVG ignores NULL values. Be mindful of integer division in some engines; cast to a decimal type when precision matters.

MIN and MAX

-- First and latest order per customer
SELECT o.customer_id,
       MIN(o.order_date) AS first_order,
       MAX(o.order_date) AS latest_order
FROM orders AS o
GROUP BY o.customer_id;

DISTINCT inside aggregates

-- Number of distinct products per order
SELECT oi.order_id,
       COUNT(DISTINCT oi.product_id) AS unique_products
FROM order_items AS oi
GROUP BY oi.order_id;

Conditional aggregates

Use CASE expressions for portable conditional sums and counts.

-- Order status breakdown per customer
SELECT o.customer_id,
       SUM(CASE WHEN o.status = 'OPEN' THEN 1 ELSE 0 END)   AS open_orders,
       SUM(CASE WHEN o.status = 'CLOSED' THEN 1 ELSE 0 END) AS closed_orders
FROM orders AS o
GROUP BY o.customer_id;

PostgreSQL and a few engines support FILTER for concise conditional aggregates:

-- PostgreSQL example
SELECT customer_id,
       COUNT(*) FILTER (WHERE status = 'OPEN')   AS open_orders,
       COUNT(*) FILTER (WHERE status = 'CLOSED') AS closed_orders
FROM orders
GROUP BY customer_id;

ROLLUP, CUBE, and GROUPING SETS

Advanced grouping operators produce multi-level totals in one query. They extend GROUP BY and return subtotal rows alongside detail rows.

ROLLUP: hierarchical subtotals

ROLLUP creates subtotals moving from right to left through the grouping list and a grand total at the end.

-- Revenue by year, then month, plus yearly totals and a grand total
SELECT EXTRACT(YEAR  FROM o.order_date) AS yr,
       EXTRACT(MONTH FROM o.order_date) AS mon,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY ROLLUP (EXTRACT(YEAR  FROM o.order_date),
                 EXTRACT(MONTH FROM o.order_date))
ORDER BY yr, mon;

Subtotal rows show NULL in the rolled-up columns. Use GROUPING or GROUPING_ID (where available) to detect them cleanly.

-- Label subtotal rows (syntax varies by engine)
SELECT
  EXTRACT(YEAR FROM o.order_date) AS yr,
  EXTRACT(MONTH FROM o.order_date) AS mon,
  SUM(oi.quantity * oi.unit_price) AS revenue,
  GROUPING(EXTRACT(YEAR FROM o.order_date))  AS g_yr,
  GROUPING(EXTRACT(MONTH FROM o.order_date)) AS g_mon
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY ROLLUP (EXTRACT(YEAR FROM o.order_date),
                 EXTRACT(MONTH FROM o.order_date));

CUBE: all combinations of groups

CUBE(a, b, c) returns subtotals for every combination of the listed columns plus the grand total. This grows quickly with each additional column.

-- Revenue by region, channel, and product category plus all subtotals
SELECT r.region, o.channel, p.category,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
JOIN regions  AS r ON r.region_id = o.region_id
GROUP BY CUBE (r.region, o.channel, p.category);

GROUPING SETS: explicit subtotal patterns

GROUPING SETS let you specify exactly which groupings you want.

-- Get revenue by region, by channel, and the grand total; avoid unwanted combos
SELECT r.region, o.channel,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN regions AS r ON r.region_id = o.region_id
GROUP BY GROUPING SETS (
  (r.region),
  (o.channel),
  ()            -- grand total
)
ORDER BY r.region NULLS LAST, o.channel NULLS LAST;

Emulating advanced groupings

If your engine lacks these operators, use UNION ALL across several grouped queries.

-- Emulate ROLLUP(year, month)
SELECT yr, mon, SUM(revenue) AS revenue
FROM (
  SELECT EXTRACT(YEAR FROM o.order_date)  AS yr,
         EXTRACT(MONTH FROM o.order_date) AS mon,
         oi.quantity * oi.unit_price      AS revenue
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
) AS t
GROUP BY yr, mon

UNION ALL
SELECT yr, NULL AS mon, SUM(revenue)
FROM (
  SELECT EXTRACT(YEAR FROM o.order_date) AS yr,
         oi.quantity * oi.unit_price     AS revenue
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
) AS y
GROUP BY yr

UNION ALL
SELECT NULL AS yr, NULL AS mon, SUM(oi.quantity * oi.unit_price)
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id;

Dialect notes

Master the basics of grouping, then add subtotals with ROLLUP, full combinatorial summaries with CUBE, or targeted totals with GROUPING SETS as your analysis requires.