Aggregate functions

Functions that reduce many rows to one value: COUNT, SUM, AVG, MIN, MAX and friends.

Syntax

count(*)  count(expr)  count(DISTINCT expr)
sum(expr)  avg(expr)  min(expr)  max(expr)
string_agg(expr, sep [ORDER BY ...])  array_agg(expr [ORDER BY ...])
bool_and(expr)  bool_or(expr)  stddev(expr)  variance(expr)
aggregate(...) FILTER (WHERE condition)

Without GROUP BY, an aggregate collapses the whole (filtered) table into one row. With GROUP BY, it produces one value per group.

count(*) counts rows; count(col) counts non-NULL values; count(DISTINCT col) counts different non-NULL values.
sum, avg, min, max ignore NULLs. Over zero rows (or only NULLs) they return NULL, while count returns 0.
sum of integer returns bigint; avg of integer/numeric returns numeric.
min/max also work on text and dates.
Aggregates cannot be nested (avg(sum(x))) — aggregate in a subquery/CTE first, or use a window function.
Aggregates are not allowed in WHERE; filter groups with HAVING.
Once you aggregate, every other column in SELECT must be in GROUP BY (or inside an aggregate).

Examples

Whole-table summary starter

SELECT count(*) AS orders,
       count(DISTINCT customer_id) AS customers,
       sum(quantity) AS units,
       min(order_date) AS first_order,
       max(order_date) AS last_order
FROM orders;

One row: aggregates without GROUP BY summarise the entire table.

Revenue from order lines shop

SELECT round(sum(quantity * unit_price - discount_amount), 2) AS item_revenue,
       round(avg(quantity * unit_price - discount_amount), 2) AS avg_line_value,
       max(quantity) AS biggest_line_qty
FROM order_items;

Aggregates accept expressions, evaluated per row before summing.

count(*) vs count(col) shop

SELECT count(*) AS customers, count(email) AS with_email, count(*) - count(email) AS without_email
FROM customers;

The difference is the number of NULL emails.

Pitfalls

count(col) when you meant count(*) (NULLs are skipped).
sum() over no rows returning NULL, not 0; wrap in COALESCE(sum(x), 0) when 0 is the right answer.
Summing after a join that duplicated rows (fan-out): totals become inflated.
Nesting aggregates: aggregate function calls cannot be nested.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

MySQL: GROUP_CONCAT instead of string_agg.
SQL Server: AVG of an integer column returns an integer (truncated); cast to decimal first. STRING_AGG ... WITHIN GROUP (ORDER BY).
BigQuery: COUNTIF(cond), LOGICAL_AND / LOGICAL_OR; ANY_VALUE.