AI Hints
Homeโ€บSQL Tutorialโ€บFiltering Groups
SQL ยท Lesson 41 of 126

Counting with Conditions

CASE inside SUM.

โฑ 2 min readโ–ถ Video at 57:18๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Counting with Conditions
From the video: Counting with Conditions
  • 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

โœ๏ธ Exercise: Small markets

Countries with fewer than 80 customers, smallest first

Show hint

GROUP BY, then HAVING COUNT(*) < 80

Show solution
solution.sqlsql
SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
HAVING COUNT(*) < 80
ORDER BY customers;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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
Next up: Why Data Lives in Many Tables โ€” keys and relationships