aggregate
COUNT
Returns the number of rows matching a query, optionally counting non-NULL values of an expression or distinct values. COUNT(*) counts every row including NULLs, while COUNT(expr) skips NULLs.
COUNT(* | [DISTINCT] expression)Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| * | Count every row regardless of NULLs (fastest for row totals) |
| DISTINCT expr | Count unique non-NULL values of expr |
| expr | Count non-NULL values of a specific column or expression |
| FILTER (WHERE ...) | Postgres: conditional counting inside aggregate |
Examples
SELECT COUNT(*) FROM orders;Total number of order rows including any with NULL columns
SELECT COUNT(DISTINCT customer_id) FROM orders;Number of unique customers who placed orders
SELECT COUNT(shipped_at) FROM orders;Only rows where shipped_at IS NOT NULL
SELECT COUNT(*) FILTER (WHERE status = 'paid') FROM orders;Postgres-only conditional count without a WHERE clause
Dialect notes / Gotcha
COUNT(column) silently ignores NULLs — use COUNT(*) for a true row count. FILTER clause is Postgres/SQLite 3.30+; MySQL requires COUNT(CASE WHEN ... THEN 1 END).
Related functions
SUM
Adds up all non-NULL numeric values in a column or expression. Returns NULL (not 0) if every input row is NULL or the result set is empty.
AVG
Computes the arithmetic mean of non-NULL numeric values. NULL rows are excluded from both the sum and the divisor, so AVG is not equivalent to SUM/COUNT(*) when NULLs are present.
CASE WHEN
SQL's portable if/else-if expression. Evaluates each WHEN clause in order and returns the first matching result; returns NULL from an unmatched CASE without ELSE.
MIN
Returns the smallest non-NULL value in a group, using natural ordering for the column's data type. Works for numbers, dates, and strings (lexicographic).
MAX
Returns the largest non-NULL value in a group by the column's natural ordering. Useful for latest timestamps, top prices, or alphabetically last strings.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet