SQL COUNT with Conditions: Conditional Aggregation Explained

The Problem

You need to count rows matching different conditions in a single query — like counting active vs inactive users, or orders by status, without running separate queries for each.

Method 1: CASE WHEN (All Databases)

SELECT COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count, COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count, COUNT(*) AS total FROM users;

The key insight: COUNT ignores NULLs, so CASE returns 1 for matches and NULL (implicit ELSE) for non-matches.

Method 2: SUM with Boolean (MySQL / PostgreSQL)

SELECT SUM(status = 'active') AS active_count, SUM(status = 'inactive') AS inactive_count FROM users;

MySQL treats boolean expressions as 0/1. In PostgreSQL, cast explicitly: SUM((status = 'active')::int).

Method 3: FILTER Clause (PostgreSQL / SQLite 3.30+)

SELECT COUNT(*) FILTER (WHERE status = 'active') AS active_count, COUNT(*) FILTER (WHERE status = 'inactive') AS inactive_count FROM users;

The cleanest syntax, but only available in PostgreSQL and newer SQLite.

Counting DISTINCT with Conditions

SELECT COUNT(DISTINCT CASE WHEN plan = 'pro' THEN user_id END) AS pro_users, COUNT(DISTINCT CASE WHEN plan = 'free' THEN user_id END) AS free_users FROM subscriptions;