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 `WIDTH_BUCKET(value, low, high, n)` to assign each row to one of `n` equal-width buckets between `low` and `high` — the foundation for building numeric histograms directly in SQL.
Divide the sum of value times weight by the sum of weights instead of averaging pre-aggregated values.
Compute the arithmetic mean of numeric values across matching rows.
Find the median or any percentile value using `PERCENTILE_CONT` in PostgreSQL, MariaDB, and SQL Server, or a window-function workaround in MySQL and SQLite.
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.
Measure the strength of a linear relationship between two columns with `CORR(y, x)` and covariance with `COVAR_SAMP` / `COVAR_POP` in PostgreSQL.
Measure how spread out values are with `STDDEV_SAMP` / `VAR_SAMP` in MySQL, MariaDB, and PostgreSQL, or `STDEV` / `VAR` in SQL Server.
Aggregate multiple string values from grouped rows into a single delimited string.
Aggregate a result set into a single row that reports how many rows matched.
Count only the distinct values in a column, ignoring duplicates.
Measure how many unique users completed each stage of a multi-step flow using `COUNT(DISTINCT CASE WHEN event_name = '...' THEN user_id END)` per step.
Flag values that fall outside the interquartile fence `[Q1 − 1.5 × IQR, Q3 + 1.5 × IQR]` using `PERCENTILE_CONT` in PostgreSQL, MariaDB, and SQL Server.
Use `HAVING` to filter aggregate results after `GROUP BY`, the same way `WHERE` filters individual rows before grouping.
Identify the value that appears most often in a column using `GROUP BY` with `ORDER BY COUNT(*) DESC LIMIT 1`, or with `MODE() WITHIN GROUP` in PostgreSQL.
Use `MIN` and `MAX` to find the lowest and highest values in a column.
Compute the least-squares slope, intercept, and R² of a linear regression directly in SQL using `REGR_SLOPE(y, x)`, `REGR_INTERCEPT(y, x)`, and `REGR_R2(y, x)` in PostgreSQL.
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.
Add numeric values across matching rows and return one aggregate total.
`COUNT(column)` skips `NULL` values, while `COUNT(*)` counts every row that made it into the result set.