COUNT with CASE

Part of the Beyond the Basics section of Coddy's SQL journey — lesson 12 of 27.

You can put a CASE expression inside an aggregate. The trick is that COUNT ignores NULL, so a CASE with no ELSE only counts rows that match the condition.

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

Now active_count is the number of 'active' rows even though we didn't filter the whole query. This is the trick behind every multi-metric dashboard query you'll ever write.

Challenge

Easy

Available tables and columns:
orders: id, customer_id, status

For each customer_id return:

customer_id
total: total orders that customer placed
shipped: count where status = 'shipped'
cancelled: count where status = 'cancelled'

Return only customers who have at least one cancelled order. Order by customer_id.

Cheat sheet

Use CASE inside COUNT to count rows matching a condition without filtering the whole query (COUNT ignores NULL, so omit the ELSE clause):

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

Try it yourself

SELECT customer_id,
       COUNT(*) AS total,
       -- shipped, cancelled using COUNT(CASE ...)
FROM orders
GROUP BY customer_id
-- filter to customers with at least one cancellation
ORDER BY customer_id