SQL aggregate functions, GROUP BY, HAVING, and distinct/advanced aggregates mapped to return values and one-line examples on an orders table.
Aggregate functions collapse many rows into one scalar result. GROUP BY partitions rows by key before aggregating. HAVING filters groups after aggregation. NULL inputs are skipped by every aggregate except COUNT(*).
Reference table · 29 entries
SQL Aggregate FunctionsExplained
29 of 29 rows
Scalar
Count of non-null input values; COUNT(*) counts all rows.
SELECT COUNT(*) FROM orders
Sum of non-null numeric values. Returns NULL on an empty set.
SELECT SUM(total) FROM orders
Arithmetic mean of non-null numeric values. NULL inputs ignored.
SELECT AVG(total) FROM orders
Smallest non-null value across any comparable type.
SELECT MIN(total) FROM orders
Largest non-null value across any comparable type.
SELECT MAX(total) FROM orders
Grouped
Partitions input rows by key; aggregates run per partition.
SELECT user_id, SUM(total) FROM orders GROUP BY user_id
Predicate evaluated per group after aggregation. Replaces WHERE for aggregates.
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 5
Several aggregates computed per group in one pass.
SELECT user_id, COUNT(*), SUM(total), AVG(total) FROM orders GROUP BY user_id
Computes several groupings in one statement; keys outside the current set return NULL.
SELECT user_id, region, SUM(total) FROM orders GROUP BY GROUPING SETS ((user_id), (region), ())
Shorthand for hierarchy-based grouping sets; adds subtotals and a grand total.
SELECT region, user_id, SUM(total) FROM orders GROUP BY ROLLUP (region, user_id)
Shorthand for every combination of the listed keys as grouping sets.
SELECT region, user_id, SUM(total) FROM orders GROUP BY CUBE (region, user_id)
Mixes plain group keys with grouping sets in one clause.
SELECT user_id, region, SUM(total) FROM orders GROUP BY user_id, GROUPING SETS ((region), ())
Keeps the first row per key in sort order. Postgres extension.
SELECT DISTINCT ON (user_id) user_id, total FROM orders ORDER BY user_id, created_at DESC
Distinct and advanced
Count of unique non-null values. Deduplicates first.
SELECT COUNT(DISTINCT user_id) FROM orders
Estimates the count of distinct values with bounded error; built for large inputs.
SELECT APPROX_COUNT_DISTINCT(user_id) FROM orders
Concatenates non-null strings with a delimiter.
SELECT STRING_AGG(user_id::text, ',') FROM orders
Concatenates non-null strings; the MySQL name for STRING_AGG.
SELECT GROUP_CONCAT(user_id SEPARATOR ',') FROM orders
Collects non-null input values into an array.
SELECT ARRAY_AGG(total) FROM orders
Collects input rows into a JSON array; the JSONB form stores parsed binary.
SELECT JSONB_AGG(user_id) FROM orders
TRUE when every / any non-null input value is true.
SELECT BOOL_AND(total > 0), BOOL_OR(total > 100) FROM orders
Standard alias for BOOL_AND.
SELECT EVERY(total > 0) FROM orders
Interpolated / nearest-rank percentile over the sorted input; requires WITHIN GROUP.
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) FROM orders
Most frequent input value; an ordered-set aggregate in Postgres.
SELECT MODE() WITHIN GROUP (ORDER BY total) FROM orders
Passes the sort order to ordered-set and hypothetical-set aggregates.
SELECT RANK(500) WITHIN GROUP (ORDER BY total) FROM orders
Restricts the aggregate to the rows that pass the condition.
SELECT COUNT(*) FILTER (WHERE total > 100) FROM orders
Sorts values before they are joined or collected by the aggregate.
SELECT STRING_AGG(user_id::text, ',' ORDER BY user_id) FROM orders
COUNT(*) and COUNT(1) count every row; COUNT(col) skips NULL inputs.
SELECT COUNT(*), COUNT(total), COUNT(1) FROM orders
Sample standard deviation of non-null numeric values. STDDEV_POP gives the population form; the bare name's default differs per engine.
SELECT STDDEV(total) FROM orders
Sample variance of non-null numeric values (square of STDDEV). VAR_SAMP and VAR_POP name each form.