How do you count things conditionally in one query?

Q: How do you count things conditionally in one query?

Put a CASE expression inside the aggregate: COUNT(CASE WHEN status = 'shipped' THEN 1 END) counts only shipped rows, because the missing ELSE yields NULL and COUNT skips NULLs. SUM(CASE WHEN … THEN total ELSE 0 END) does the same for a measure. Some engines also support COUNT(*) FILTER (WHERE status = 'shipped'), which is clearer where available. This pattern replaces several queries or a self-join with one pass.

Question

Accepted Answer

Put a CASE expression inside the aggregate:

COUNT(CASE WHEN status = 'shipped' THEN 1 END) counts only shipped rows, because the missing ELSE yields NULL and COUNT skips NULLs. SUM(CASE WHEN … THEN total ELSE 0 END) does the same for a measure.

Some engines also support COUNT(*) FILTER (WHERE status = 'shipped'), which is clearer where available. This pattern replaces several queries or a self-join with one pass.