Summarizing Data
Individual rows tell you facts. Aggregations tell you patterns. The GROUP BY clause collapses multiple rows that share a common value into a single summary row, and aggregate functions compute statistics across those rows.
The core aggregate functions:
| Function | Description |
|---|---|
COUNT(*) |
Number of rows in the group |
COUNT(col) |
Number of non-NULL values in the column |
SUM(col) |
Total of all values |
AVG(col) |
Mean value |
MIN(col) / MAX(col) |
Smallest / largest value |
-- How much has each customer spent in total?
SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC;
WHERE vs. HAVING: The Critical Distinction
Both WHERE and HAVING are filters, but they operate at different stages of the execution pipeline:
- WHERE filters individual rows before grouping. It cannot reference aggregate functions because the aggregation hasn't happened yet.
- HAVING filters groups after aggregation. It can reference aggregate functions.
-- Find customers who spent more than $500 total, only counting 2023 orders
SELECT user_id, SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2023-01-01' -- Filter rows first
GROUP BY user_id
HAVING SUM(amount) > 500; -- Then filter groups
Rule of thumb: If your filter references a raw column value, use WHERE. If it references an aggregate (COUNT, SUM, AVG), use HAVING.
A subtle performance note: WHERE filters happen early and reduce the number of rows that need to be grouped and aggregated. Always push filters into WHERE when possible — it's faster than filtering with HAVING because you're doing less work.