Counting with Conditions
CASE inside SUM.
Helen wants one neat summary row. Total orders, cancelled orders, and returned orders, side by side. Group by would give them as separate rows. But there's a famous trick to put them in columns.

- One summary row
- Total, cancelled, returned
- Side by side, in columns
CASE inside SUM
Here's the trick. For each order, the case gives a one if it was cancelled, and a zero if not. Sum adds up all those ones, so it counts the cancelled orders. We do the same for returned. Run it. Out of one thousand five hundred and ninety eight orders, one hundred and forty were cancelled, and sixty three were returned.
SELECT COUNT(*) AS total,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returned
FROM orders;โโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโโ โ total โ cancelled โ returned โ โโโโโโโโโผโโโโโโโโโโโโผโโโโโโโโโโโค โ 1598 โ 140 โ 63 โ โโโโโโโโโดโโโโโโโโโโโโดโโโโโโโโโโโ
With GROUP BY too
The trick gets even better with group by. Now we get the total and the cancellations for each year, side by side. Run it. The number of orders almost doubled, but cancellations went up two and a half times. Helen now knows exactly what to investigate.
SELECT strftime('%Y', order_date) AS year,
COUNT(*) AS orders,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY year;โโโโโโโโฌโโโโโโโโโฌโโโโโโโโโโโโ โ year โ orders โ cancelled โ โโโโโโโโผโโโโโโโโโผโโโโโโโโโโโโค โ 2024 โ 559 โ 40 โ โ 2025 โ 1039 โ 100 โ โโโโโโโโดโโโโโโโโโดโโโโโโโโโโโโ
Small markets
Your turn. Which countries have fewer than eighty customers? Show the country and its number of customers, smallest first. Pause and try. Here's a solution. Group by country, having count less than eighty. Canada, Ireland, and Germany are Helen's smaller markets.
Countries with fewer than 80 customers, smallest first
Show hint
GROUP BY, then HAVING COUNT(*) < 80
Show solution
SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
HAVING COUNT(*) < 80
ORDER BY customers;โโโโโโโโโโโฌโโโโโโโโโโโโ โ country โ customers โ โโโโโโโโโโโผโโโโโโโโโโโโค โ Germany โ 42 โ โ Ireland โ 52 โ โ Canada โ 77 โ โโโโโโโโโโโดโโโโโโโโโโโโ
Summary
Case inside sum counts things that match a condition, side by side. You've now mastered summarising data. And so far, every query has used just one table. Next, the most important skill in SQL. Joining tables together.
Key takeaways- SUM(CASE WHEN ... THEN 1 ELSE 0 END)
- Works with GROUP BY
- Side-by-side summaries