COUNT
Count rows or non-NULL values — and know which one you're actually counting.
Syntax
COUNT(*)
COUNT(expression)
COUNT(DISTINCT expression)| Parameter | Type | Notes |
|---|---|---|
| * | literal | Counts every row in the group, NULLs included. |
| expression | any | Counts rows where the expression is not NULL. Add |
COUNT(*) vs COUNT(column)
Loading editor…
COUNT(*) counted every order. COUNT(cancelled_at) only counted the ones
where that column isn't NULL — i.e. orders that were actually cancelled.
Swapping COUNT(*) for COUNT(some_column) is one of the most common silent
bugs in a report: the numbers only match when that column has no NULLs.
Per group
Loading editor…
Counting a subset with CASE
COUNT only takes one predicate implicitly (NULL vs not-NULL) — to count rows
matching an arbitrary condition, wrap it in CASE and let the implicit NULL
rule do the filtering:
Loading editor…
No ELSE on purpose — non-matching rows fall through to NULL, which COUNT
skips. See Pivot without PIVOT for the full pattern.
Gotchas
COUNT(*)andCOUNT(column)disagree the momentcolumncan beNULL— decide which one the report actually needs.- a
WHEREfilter changes the row set beforeCOUNTruns; aLEFT JOINcan inflate or misrepresentCOUNT(*)if the join fans out (see INNER JOIN: fan-out). COUNT(DISTINCT a, b)(multiple columns) isn't standard — MySQL supports it, Postgres and SQL Server don't; useCOUNT(DISTINCT ...)over a concatenated key or a subquery instead.
- SUM — same NULL-skipping behavior, different aggregate.
- GROUP BY — the grain COUNT is computed over.
- Deduplicate rows — using COUNT(*) to find the duplicates first.
Safety checklist
- decide whether NULLs should count or be excluded
- confirm the correct grain before grouping
- check whether join fan-out is inflating the count