Aggregation and pivots: SQL patterns and examples
Conditional counts, pivots, subtotals and weighted averages. The common failure is aggregating at the wrong grain, so check what one row of your table represents first.
- Conditional AggregationConditional aggregation counts or sums only the rows that meet a condition, so several breakdowns come back from a single pass over the table.
- Pivot Rows to ColumnsPivoting turns the values of one column into separate columns, such as one column per quarter, so a table reads like a spreadsheet.
- Count DistinctCount distinct counts each different value once, which is how you get unique users, customers or products instead of rows.
- HAVING: Filter GroupsHAVING filters groups after they have been aggregated, such as customers with at least three orders, while WHERE filters rows before.
- Weighted AverageA weighted average gives each value an importance, such as quantity, so a price on 1,000 units counts for more than a price on 10.
- Subtotals with ROLLUPROLLUP adds subtotal and grand total rows to a grouped query, so one result shows detail, subtotals per group and the overall total.
- Histogram BucketsHistogram buckets group a numeric column into equal-width ranges and count the rows in each, showing how values are distributed.