← All Kits · SQL Kit

COUNT with CASE WHEN: Counting Some of the Rows Without Filtering the Rest Away

Michael Nocito · Updated August 2026 · Every query on this page was run before it was published

You need shipped orders, cancelled orders and total orders in one result. Running three queries and pasting the answers together works and does not survive a change of date range. The one-query version puts a condition inside the aggregate.

What you do: COUNT(CASE WHEN condition THEN 1 END), with no ELSE. On the eight orders below, that returns 5 shipped. Add ELSE 0 and the same query returns 8.

The short version. COUNT counts values and ignores NULL. That single fact is the whole technique.

The table

CREATE TABLE orders(order_id INT, customer_id INT, amount INT, status TEXT);
INSERT INTO orders VALUES
(101,1,120,'shipped'),(102,1,80,'shipped'),(103,2,200,'cancelled'),
(104,2,50,'shipped'),(105,3,300,'shipped'),(106,4,90,'pending'),
(107,4,60,'shipped'),(108,5,150,'cancelled');

Eight orders: five shipped, two cancelled, one pending. Total amount 1,050.

The query

SELECT
  COUNT(*)                                          AS all_orders,
  COUNT(CASE WHEN status = 'shipped'   THEN 1 END)  AS shipped,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END)  AS cancelled,
  SUM(CASE WHEN status = 'shipped' THEN amount ELSE 0 END) AS shipped_value
FROM orders;

One pass over the table, four answers, and every one of them respects whatever WHERE clause you add later. That is the practical advantage over three separate queries: the filters can never drift apart.

The ELSE that breaks it

COUNT(CASE WHEN status = 'shipped' THEN 1 END)          -- 5, correct
COUNT(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END)   -- 8, wrong

A CASE with no matching WHEN and no ELSE returns NULL, and COUNT skips NULLs. Write ELSE 0 and every row now produces a zero, which is a value, so COUNT counts all eight.

No error, no warning, and the number is still plausible. This is the most common mistake in conditional aggregation and it survives review easily, because ELSE 0 looks like defensive good practice.

Three of those four are right. The recommendation is to pick one form and use it consistently, because then a stray ELSE looks wrong on sight instead of looking careful.

Rates, in the same query

SELECT
  COUNT(*) AS orders,
  COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped,
  ROUND(100.0 * COUNT(CASE WHEN status = 'shipped' THEN 1 END) / COUNT(*), 1)
    AS shipped_pct
FROM orders;
-- 8, 5, 62.5

The 100.0 matters. In databases with integer division, 5 / 8 is 0, and the rate comes back as zero for every row. Multiply by a decimal first, or cast one side.

Guard the denominator too. COUNT(*) cannot be zero when a row exists, but a conditional denominator can be, and a division by zero either errors or returns NULL depending on the engine. NULLIF(denominator, 0) makes the behaviour the same everywhere.

Combined with GROUP BY

SELECT customer_id,
       COUNT(*) AS orders,
       COUNT(CASE WHEN status = 'shipped'   THEN 1 END) AS shipped,
       COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

This is the shape that makes conditional counts worth learning: one row per customer, several measures across the columns, and no self joins or repeated subqueries. It is the SQL equivalent of a pivot table, and it is how most report tables are actually produced.

The FILTER clause, where you have it

SELECT
  COUNT(*)                                   AS orders,
  COUNT(*) FILTER (WHERE status = 'shipped') AS shipped,
  SUM(amount) FILTER (WHERE status = 'shipped') AS shipped_value
FROM orders;

Standard SQL, supported by PostgreSQL and SQLite. It says exactly what it means and has no ELSE to get wrong. SQL Server and MySQL do not support it, so the CASE form remains the portable answer and is worth being fluent in regardless.

How to apply this to your own work

1. Find a report built from several near-identical queries and collapse it into one with conditional aggregates.
2. Search your saved queries for COUNT(CASE and check every one for an ELSE.
3. Add the sanity check: the category counts must sum to the total.
4. Multiply by 100.0 in every rate, and wrap denominators in NULLIF.
5. Use FILTER where your database supports it, and note in a comment why the other queries do not.

The one habit to keep

Ask what a non-matching row produces. NULL and skipped, or 0 and counted. Every conditional aggregate in SQL comes down to that question, and the answer changes with the function you wrapped it in.

Is there a conditional count in your reporting that happens to equal the total row count?