Grouping and Aggregates

Summarize data with GROUP BY and aggregate functions.

GROUP BY and aggregates

SELECT customer_id, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
GROUP BY customer_id;

All selected non-aggregated columns must appear in GROUP BY (or be functionally dependent if your engine allows).

HAVING (filter groups)

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 5;  -- only prolific customers

HAVING applies after grouping; WHERE filters rows before grouping.

DISTINCT aggregates

SELECT COUNT(DISTINCT customer_id) FROM orders;

Counts unique customers.

Grouping sets / rollup / cube (engine-specific)

-- Postgres example
SELECT region, product, SUM(total)
FROM sales
GROUP BY ROLLUP (region, product);

Generates subtotals and grand totals.

Averages and nulls

Aggregates ignore NULL (except COUNT(*)). Use COALESCE if you need zeros.

SELECT COALESCE(SUM(x), 0) FROM t;

Summary

• Use GROUP BY with aggregates; filter groups via HAVING
• DISTINCT inside aggregates removes duplicates; rollups add subtotals