TopolearnCounts, totals and averages are the first questions anyone asks of data.
An aggregate function takes a set of rows and returns a single value. Without GROUP BY, the whole filtered table becomes one group and the query returns exactly one row:
SELECT COUNT(*), SUM(total), AVG(total) FROM orders WHERE status = 'shipped';The core five are COUNT, SUM, AVG, MIN and MAX, and they share one rule: aggregates ignore NULLs (except COUNT(*)). That gives three different counts:
COUNT(*)counts rows.COUNT(city)counts rows wherecityis not NULL.COUNT(DISTINCT city)counts different non-NULL values.
Ignoring NULLs also affects AVG: the average of 10, NULL and 20 is 15, not 10. If missing should count as zero, write AVG(COALESCE(x, 0)).
Over zero rows, COUNT returns 0 but SUM, AVG, MIN and MAX return NULL, which surprises code that expects SUM of nothing to be 0. Wrap it: COALESCE(SUM(total), 0).
The standard FILTER clause (supported by PostgreSQL and SQLite) applies a condition to one aggregate only, so a single pass can compute several counts:
SELECT COUNT(*) FILTER (WHERE status = 'shipped') AS shipped,
COUNT(*) FILTER (WHERE status = 'pending') AS pending
FROM orders;Elsewhere, write SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END).
Finally, once you aggregate, you cannot also select plain columns: SELECT name, COUNT(*) FROM customers is an error, because there is one count but four names. To get one result per name or city, you need GROUP BY, the next lesson.
Example
SELECT COUNT(*) AS orders,
SUM(total) AS revenue,
ROUND(AVG(total), 2) AS avg_order,
MAX(total) AS biggest
FROM orders
WHERE status = 'shipped';
-- orders | revenue | avg_order | biggest
-- 3 | 316.49 | 105.50 | 238.00
SELECT COUNT(*) AS customers, COUNT(city) AS with_city,
COUNT(DISTINCT city) AS cities
FROM customers;
-- customers | with_city | cities
-- 4 | 3 | 2
SELECT SUM(total) AS s, COALESCE(SUM(total), 0) AS safe
FROM orders WHERE status = 'refunded';
-- s | safe
-- NULL | 0Remember
- Aggregates return one value per group; without GROUP BY, one row total
- COUNT(*) counts rows; COUNT(col) skips NULLs; other aggregates ignore NULLs too
- SUM/AVG/MIN/MAX of zero rows is NULL, not 0
- FILTER (WHERE ...) restricts a single aggregate
Watch out for
- Reporting COUNT(column) as a row count when the column contains NULLs.