Tag archive
Commands grouped around the same concept, pattern, or recurring problem.
Use `GROUP BY ROLLUP` to produce subtotals and a grand total alongside regular grouped rows in one query.
Produce multiple independent aggregation levels in one query using `GROUPING SETS` — more flexible than `ROLLUP` because you choose exactly which combinations to include.
Use `CASE WHEN` inside `SUM` or `COUNT` to produce multiple metrics from a single pass over grouped data.
Collect values from multiple rows into a single JSON array, ordered and grouped by other columns.
Count or sum only the rows that match a specific condition without a WHERE clause — using `FILTER (WHERE ...)` in PostgreSQL and SQLite, or `CASE WHEN` inside the aggregate for other engines.
Use `GROUP BY CUBE(col_a, col_b)` to generate subtotals for every possible combination of those columns — the full power set — in a single query.
Aggregate multiple string values from grouped rows into a single delimited string.
Use `HAVING` to filter aggregate results after `GROUP BY`, the same way `WHERE` filters individual rows before grouping.
Group rows by the candidate key and keep only values that appear more than once.
Aggregate rows by category with `GROUP BY` and count how many rows fall into each group.
Bucket dates into month starts, then aggregate counts or totals per month.
Use more than one column in `GROUP BY` when each combination defines a separate aggregate bucket.
Turn distinct row values into separate columns using `CASE WHEN` inside an aggregate.
`WHERE` filters individual rows before grouping. `HAVING` filters grouped results after aggregates are computed.