skip to today's puzzle
SQL Sip

CASE inside COUNT counts only matching rows

Sometimes one report needs several totals at once, like completed and cancelled orders. Put CASE inside COUNT: CASE gives back a value when the row matches and NULL when it doesn't. COUNT skips NULLs, so each column only counts its own kind of row. The same trick works inside SUM to add up only some rows.

A tiny example

In plain English: Tell me how many orders were completed and how many were cancelled, side by side.

SELECT COUNT(CASE
  WHEN status = 'completed' THEN 1
END) AS completed, COUNT(CASE
  WHEN status = 'cancelled' THEN 1
END) AS cancelled
FROM orders;

What comes back

completedcancelled
425

One row comes back with two totals: 42 orders were completed and 5 were cancelled. Rows with any other status are counted in neither column.

Related SQL lessons

Ready for a break?

Take on today's cozy puzzle. A fresh, bite-sized query waits every morning — no timers, no pressure.

play today's sip