Chapter 4 · Functions and Summaries
CASE WHEN, Conditional Counts and Business Questions
- Page 15 of 22
- 18 min read
Real questions come with conditions. "How many orders did each customer have, and how many of those were delivered?" "Split products into budget, mid-range and premium." "Is this customer a repeat buyer: yes or no?" CASE is SQL's if-then-else, and combined with GROUP BY it answers most of the questions a manager, an analyst or a data scientist will bring you. It is also how you build labels and categorical features for a model straight from the database: price bands, yes/no flags, one column per category.
What you will learn
- Searched
CASE WHENand simpleCASE, and how they treatELSEand NULL. - Bucketing values into bands and counting each band.
- Conditional aggregation:
SUM(CASE …),COUNT(CASE …)andFILTER (WHERE …). - Grouping by month, percentages of a total, and multi-level totals with
ROLLUP. - Finding duplicates and missing categories, and turning business questions into SQL.
CASE WHEN: if-then-else in a query
The searched form checks conditions from top to bottom and returns the value of the first one that is true. If none is true, it returns the ELSE value:
SELECT product_id, name, price,
CASE
WHEN price < 2000 THEN 'budget'
WHEN price < 5000 THEN 'mid' -- only reached if price >= 2000
ELSE 'premium'
END AS band
FROM products
ORDER BY price, product_id;+------------+-----------------------------+-------+---------+
| product_id | name | price | band |
+------------+-----------------------------+-------+---------+
| 4 | Wireless Mouse | 900 | budget |
| 11 | Gift Card | 1000 | budget |
| 1 | Python Crash Course | 1200 | budget |
| 8 | Laptop Stand | 1500 | budget |
| 9 | USB-C Hub | 2200 | mid |
| 2 | Hands-On Machine Learning | 2500 | mid |
| 6 | SQL Masterclass | 3000 | mid |
| 3 | Mechanical Keyboard | 4500 | mid |
| 10 | Noise-Cancelling Headphones | 7500 | premium |
| 7 | AI Engineering Bootcamp | 15000 | premium |
| 5 | 27-inch Monitor | 18500 | premium |
+------------+-----------------------------+-------+---------+Because the first match wins, the second condition does not need price >= 2000 AND. The whole CASE … END is one expression, so it can have an alias and can be used anywhere a value can.
The simple form compares one expression with a list of values. Leave out ELSE and anything unmatched becomes NULL, like cancelled order 5 here:
SELECT order_id, status,
CASE status
WHEN 'delivered' THEN 'done'
WHEN 'shipped' THEN 'on the way'
WHEN 'pending' THEN 'waiting'
END AS label
FROM orders
WHERE order_id = 5 OR order_id >= 11
ORDER BY order_id;+----------+-----------+------------+
| order_id | status | label |
+----------+-----------+------------+
| 5 | cancelled | NULL |
| 11 | delivered | done |
| 12 | shipped | on the way |
| 13 | pending | waiting |
| 14 | delivered | done |
+----------+-----------+------------+CASE also works in ORDER BY, for an order that is neither alphabetical nor numeric. A support team wants the orders that need action first:
SELECT order_id, status
FROM orders
ORDER BY CASE status WHEN 'pending' THEN 1 WHEN 'shipped' THEN 2 WHEN 'cancelled' THEN 3 ELSE 4 END,
order_id
LIMIT 5;+----------+-----------+
| order_id | status |
+----------+-----------+
| 13 | pending |
| 12 | shipped |
| 5 | cancelled |
| 1 | delivered |
| 2 | delivered |
+----------+-----------+Bucketing: count per band
Group by the CASE expression and you get a count per bucket, the SQL version of a histogram. Sorting by MIN(price) puts the bands in their natural order instead of alphabetical:
SELECT CASE
WHEN price < 2000 THEN 'budget'
WHEN price < 5000 THEN 'mid'
ELSE 'premium'
END AS band,
COUNT(*) AS products,
MIN(price) AS from_price,
MAX(price) AS to_price
FROM products
GROUP BY band
ORDER BY MIN(price);+---------+----------+------------+----------+
| band | products | from_price | to_price |
+---------+----------+------------+----------+
| budget | 4 | 900 | 1500 |
| mid | 4 | 2200 | 4500 |
| premium | 3 | 7500 | 18500 |
+---------+----------+------------+----------+SQLite, MySQL and PostgreSQL all accept the alias band in GROUP BY; strict standard SQL (and SQL Server) needs the whole CASE repeated there. Bucketing is how you turn a number into a category feature (age group, price tier, order-size class) when a model or a report needs ranges rather than exact values.
Conditional aggregation: many counts in one pass
Put a CASE inside an aggregate and each aggregate counts only the rows you choose. One query gives a column per status, a small "pivot table":
SELECT customer_id,
COUNT(*) AS orders,
SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
COUNT(CASE WHEN status IN ('pending', 'shipped') THEN 1 END) AS open_orders,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY customer_id
ORDER BY customer_id;+-------------+--------+-----------+-------------+-----------+
| customer_id | orders | delivered | open_orders | cancelled |
+-------------+--------+-----------+-------------+-----------+
| 1 | 3 | 3 | 0 | 0 |
| 2 | 3 | 2 | 1 | 0 |
| 3 | 3 | 3 | 0 | 0 |
| 4 | 1 | 0 | 0 | 1 |
| 5 | 1 | 1 | 0 | 0 |
| 6 | 2 | 1 | 1 | 0 |
| 7 | 1 | 1 | 0 | 0 |
+-------------+--------+-----------+-------------+-----------+Two equivalent patterns: SUM(CASE … THEN 1 ELSE 0 END) adds ones and zeros; COUNT(CASE … THEN 1 END) counts the non-NULL results, and the missing ELSE makes the rest NULL. The same idea works with amounts: SUM(CASE WHEN method = 'bkash' THEN amount ELSE 0 END).
PostgreSQL (and SQLite 3.30+) offer a cleaner standard syntax, FILTER. MySQL has no FILTER, but a comparison there is already 1 or 0, so SUM(condition) counts:
-- PostgreSQL
SELECT customer_id,
COUNT(*) AS orders,
COUNT(*) FILTER (WHERE status = 'delivered') AS delivered,
COUNT(*) FILTER (WHERE status IN ('pending', 'shipped')) AS open_orders
FROM orders
GROUP BY customer_id
ORDER BY customer_id
LIMIT 3;+-------------+--------+-----------+-------------+
| customer_id | orders | delivered | open_orders |
+-------------+--------+-----------+-------------+
| 1 | 3 | 3 | 0 |
| 2 | 3 | 2 | 1 |
| 3 | 3 | 3 | 0 |
+-------------+--------+-----------+-------------+-- MySQL
SELECT customer_id,
COUNT(*) AS orders,
SUM(status = 'delivered') AS delivered,
SUM(status IN ('pending', 'shipped')) AS open_orders
FROM orders
GROUP BY customer_id
ORDER BY customer_id
LIMIT 3;+-------------+--------+-----------+-------------+
| customer_id | orders | delivered | open_orders |
+-------------+--------+-----------+-------------+
| 1 | 3 | 3 | 0 |
| 2 | 3 | 2 | 1 |
| 3 | 3 | 3 | 0 |
+-------------+--------+-----------+-------------+Grouping by month
Time series are the bread and butter of analytics. In SQLite, turn each date into its year-month text with strftime('%Y-%m', …) (see Text, Number and Date Functions, CAST and COALESCE) and group by that. Money received per month, split by payment method:
SELECT strftime('%Y-%m', paid_on) AS month,
COUNT(*) AS payments,
SUM(amount) AS received,
SUM(CASE WHEN method = 'bkash' THEN amount ELSE 0 END) AS bkash,
SUM(CASE WHEN method = 'card' THEN amount ELSE 0 END) AS card,
SUM(CASE WHEN method = 'cash' THEN amount ELSE 0 END) AS cash
FROM payments
GROUP BY month
ORDER BY month;+---------+----------+----------+-------+-------+------+
| month | payments | received | bkash | card | cash |
+---------+----------+----------+-------+-------+------+
| 2026-01 | 2 | 6600 | 2100 | 4500 | 0 |
| 2026-02 | 2 | 7000 | 3000 | 0 | 4000 |
| 2026-03 | 4 | 21400 | 7400 | 14000 | 0 |
| 2026-04 | 2 | 23450 | 0 | 23450 | 0 |
| 2026-05 | 2 | 8500 | 5500 | 3000 | 0 |
| 2026-06 | 1 | 3100 | 0 | 0 | 3100 |
+---------+----------+----------+-------+-------+------+April is the best month, thanks to one 18,500-taka monitor paid by card. Text like '2026-03' sorts correctly because it is zero-padded, year first. On the servers, use their formatting functions:
-- PostgreSQL
SELECT TO_CHAR(paid_on, 'YYYY-MM') AS month, SUM(amount) AS received
FROM payments
GROUP BY month
ORDER BY month;+---------+----------+
| month | received |
+---------+----------+
| 2026-01 | 6600 |
| 2026-02 | 7000 |
| 2026-03 | 21400 |
| 2026-04 | 23450 |
| 2026-05 | 8500 |
| 2026-06 | 3100 |
+---------+----------+MySQL writes DATE_FORMAT(paid_on, '%Y-%m'); PostgreSQL also has DATE_TRUNC('month', paid_on), which keeps a real date. One limit to know: a month with no payments simply has no row. Filling the gaps needs a calendar table, built with a recursive CTE in the advanced tutorial (see CTEs and Recursive Queries).
Percentages and one-row summaries
Conditional sums divided by the total give shares, all in one row. Payment mix by amount:
SELECT ROUND(SUM(CASE WHEN method = 'bkash' THEN amount END) * 100.0 / SUM(amount), 1) AS bkash_pct,
ROUND(SUM(CASE WHEN method = 'card' THEN amount END) * 100.0 / SUM(amount), 1) AS card_pct,
ROUND(SUM(CASE WHEN method = 'cash' THEN amount END) * 100.0 / SUM(amount), 1) AS cash_pct
FROM payments;+-----------+----------+----------+
| bkash_pct | card_pct | cash_pct |
+-----------+----------+----------+
| 25.7 | 64.2 | 10.1 |
+-----------+----------+----------+Remember the * 100.0: with integer amounts, plain * 100 would do integer division and give 25, 64 and 10.
Subtotals and grand totals: ROLLUP
Reports often want each group plus a total row. PostgreSQL has GROUP BY ROLLUP (…) and MySQL GROUP BY … WITH ROLLUP. The extra row has NULL in the grouped column, which COALESCE relabels, and GROUPING(method) is 1 on that row, which keeps it last:
-- PostgreSQL
SELECT COALESCE(method, 'ALL') AS method, COUNT(*) AS payments, SUM(amount) AS total
FROM payments
GROUP BY ROLLUP (method)
ORDER BY GROUPING(method), total DESC;+--------+----------+-------+
| method | payments | total |
+--------+----------+-------+
| card | 6 | 44950 |
| bkash | 5 | 18000 |
| cash | 2 | 7100 |
| ALL | 13 | 70050 |
+--------+----------+-------+-- MySQL
SELECT COALESCE(method, 'ALL') AS method, COUNT(*) AS payments, SUM(amount) AS total
FROM payments
GROUP BY method WITH ROLLUP
ORDER BY GROUPING(method), total DESC;+--------+----------+-------+
| method | payments | total |
+--------+----------+-------+
| card | 6 | 44950 |
| bkash | 5 | 18000 |
| cash | 2 | 7100 |
| ALL | 13 | 70050 |
+--------+----------+-------+With two columns, ROLLUP (month, method) gives a row per month and method, a subtotal per month, and a grand total. SQLite has no ROLLUP; there you add the total with a second query, or stack the two with UNION ALL (see UNION, INTERSECT and EXCEPT).
Finding duplicates
"Which values appear more than once?" is GROUP BY plus HAVING COUNT(*) > 1. In the payments table:
SELECT order_id, COUNT(*) AS payments, SUM(amount) AS paid
FROM payments
GROUP BY order_id
HAVING COUNT(*) > 1;+----------+----------+-------+
| order_id | payments | paid |
+----------+----------+-------+
| 6 | 2 | 15000 |
+----------+----------+-------+Order 6 was paid in two parts (5,000 by bKash, then 10,000 by card). That is legitimate, not an error: always look at duplicates before deleting anything. Real duplicates usually hide behind small differences. Here is a newsletter sign-up list where people typed their email in different ways:
CREATE TABLE signups (signup_id INTEGER PRIMARY KEY, email TEXT NOT NULL, signed_up_on TEXT NOT NULL);
INSERT INTO signups (email, signed_up_on) VALUES
('nadia@example.com', '2026-03-01'),
('Nadia@Example.com ', '2026-03-04'),
('tanvir@example.com', '2026-03-05'),
('mitu@example.com', '2026-03-07'),
(' NADIA@example.com', '2026-03-09'),
('mitu@example.com', '2026-03-10');
SELECT email, COUNT(*) AS n FROM signups GROUP BY email HAVING COUNT(*) > 1;
SELECT LOWER(TRIM(email)) AS clean_email,
COUNT(*) AS n,
MIN(signup_id) AS keep_id,
MIN(signed_up_on) AS first_signup
FROM signups
GROUP BY clean_email
HAVING COUNT(*) > 1
ORDER BY clean_email;+------------------+---+
| email | n |
+------------------+---+
| mitu@example.com | 2 |
+------------------+---+
+-------------------+---+---------+--------------+
| clean_email | n | keep_id | first_signup |
+-------------------+---+---------+--------------+
| mitu@example.com | 2 | 4 | 2026-03-07 |
| nadia@example.com | 3 | 1 | 2026-03-01 |
+-------------------+---+---------+--------------+Grouping on the raw text finds only Mitu's exact repeat; normalising first reveals that Nadia signed up three times. MIN(signup_id) tells you which row to keep. In ML this matters a lot: duplicate examples overweight some records, and a duplicate that lands in both the training and the test set makes the model look better than it is.
Missing categories: a first look at LEFT JOIN
"How many products does each category have?" From the products table alone:
SELECT category_id, COUNT(*) AS products
FROM products
GROUP BY category_id
ORDER BY category_id;+-------------+----------+
| category_id | products |
+-------------+----------+
| NULL | 1 |
| 1 | 2 |
| 2 | 4 |
| 3 | 2 |
| 4 | 2 |
+-------------+----------+Two problems: the gift card forms a NULL group, and Furniture (category 5) is missing entirely, because GROUP BY can only make groups from rows that exist, and no product row says "5". An empty category is invisible unless you start from the categories table. That takes a join:
Preview (explained properly in LEFT, RIGHT, FULL, CROSS and Self Joins): a
LEFT JOINkeeps every category and attaches its products, if any.COUNT(p.product_id)counts only real matches, so Furniture gets 0. The short namescandpare table aliases, introduced in Joins: INNER JOIN and Table Aliases. For now, just read the result.
-- Preview: LEFT JOIN keeps categories that have no products
SELECT c.category_id, c.name, COUNT(p.product_id) AS products
FROM categories AS c
LEFT JOIN products AS p ON p.category_id = c.category_id
GROUP BY c.category_id, c.name
ORDER BY c.category_id;+-------------+-------------+----------+
| category_id | name | products |
+-------------+-------------+----------+
| 1 | Books | 2 |
| 2 | Electronics | 4 |
| 3 | Courses | 2 |
| 4 | Accessories | 2 |
| 5 | Furniture | 0 |
+-------------+-------------+----------+Business questions, answered
Put together, these tools answer real questions in a single query each.
What share of orders were cancelled, and how many are still open?
SELECT COUNT(*) AS orders,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
SUM(CASE WHEN status IN ('pending', 'shipped') THEN 1 ELSE 0 END) AS still_open,
ROUND(AVG(CASE WHEN status = 'cancelled' THEN 1.0 ELSE 0 END) * 100, 1) AS cancel_pct
FROM orders;+--------+-----------+------------+------------+
| orders | cancelled | still_open | cancel_pct |
+--------+-----------+------------+------------+
| 14 | 1 | 2 | 7.1 |
+--------+-----------+------------+------------+The average of a 0/1 column is a rate: 1 of 14 orders is 7.1%.
Which customers are repeat buyers? This is a typical label for a "will they come back?" model: 1 if the customer has at least two non-cancelled orders, else 0. A CASE can test an aggregate:
SELECT customer_id,
COUNT(*) AS orders,
CASE WHEN COUNT(*) >= 2 THEN 1 ELSE 0 END AS repeat_buyer
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
ORDER BY customer_id;+-------------+--------+--------------+
| customer_id | orders | repeat_buyer |
+-------------+--------+--------------+
| 1 | 3 | 1 |
| 2 | 3 | 1 |
| 3 | 3 | 1 |
| 5 | 1 | 0 |
| 6 | 2 | 1 |
| 7 | 1 | 0 |
+-------------+--------+--------------+Customers 4 (only a cancelled order) and 8 (no orders) have no row here; a training table must include them with label 0, which is again a LEFT JOIN from customers (also in LEFT, RIGHT, FULL, CROSS and Self Joins). Watching for who is missing from a result is half of good analysis.
Where this shows up in real systems
- Feature engineering: one-hot columns (
CASE WHEN city = 'Dhaka' THEN 1 ELSE 0 END AS is_dhaka), binned numbers and yes/no flags are built in SQL before the data reaches scikit-learn. - LLM evaluation reports:
COUNT(*) FILTER (WHERE verdict = 'correct')per model and prompt version, side by side in one table. - Business dashboards: monthly revenue by channel, cancellation rate, payment mix: conditional aggregation grouped by month.
Common mistakes
- Conditions in the wrong order. The first true
WHENwins, soWHEN price > 1000 … WHEN price > 5000 …never reaches the second branch. Fix: go from most specific to least, or from low to high with<. - Forgetting NULL. NULL matches no
WHENand falls intoELSE:CASE WHEN stock = 0 THEN 'out' ELSE 'ok' ENDcalls the courses (stock NULL) "ok". Fix: handle it first,WHEN stock IS NULL THEN 'not tracked'. COUNT(CASE … THEN 1 ELSE 0 END). 0 is not NULL, soCOUNTcounts every row. Fix: drop theELSEwithCOUNT, or useSUM(… THEN 1 ELSE 0 END).- Integer percentages.
part * 100 / totaltruncates. Fix:part * 100.0 / total, thenROUND. - Deleting "duplicates" without looking. Order 6's two payments are real. Fix: inspect with
GROUP BY … HAVING COUNT(*) > 1, normalise text first, and decide which row to keep (MIN(id)) before anyDELETE.
Try it yourself
- Easy: Label each product's stock as
'not tracked'(NULL),'out of stock'(0),'low'(under 10) or'ok', and count the products in each label. Order by count (most first), then label. - Medium: For each month of
order_date, show the number of orders, how many were delivered, and how many were not delivered (any other status). Order by month. - Hard: Build one row per customer from the orders table with: total orders, delivered orders, a
delivered_ratein percent (1 decimal), the month of their last order, and asegment:'loyal'for 3 or more orders,'returning'for 2,'new'for 1. Order by orders (most first), thencustomer_id.
Answers
-- Easy
SELECT CASE
WHEN stock IS NULL THEN 'not tracked'
WHEN stock = 0 THEN 'out of stock'
WHEN stock < 10 THEN 'low'
ELSE 'ok'
END AS stock_label,
COUNT(*) AS products
FROM products
GROUP BY stock_label
ORDER BY products DESC, stock_label;+--------------+----------+
| stock_label | products |
+--------------+----------+
| ok | 5 |
| not tracked | 3 |
| low | 2 |
| out of stock | 1 |
+--------------+----------+-- Medium
SELECT strftime('%Y-%m', order_date) AS month,
COUNT(*) AS orders,
SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
SUM(CASE WHEN status <> 'delivered' THEN 1 ELSE 0 END) AS not_delivered
FROM orders
GROUP BY month
ORDER BY month;+---------+--------+-----------+---------------+
| month | orders | delivered | not_delivered |
+---------+--------+-----------+---------------+
| 2026-01 | 2 | 2 | 0 |
| 2026-02 | 3 | 2 | 1 |
| 2026-03 | 3 | 3 | 0 |
| 2026-04 | 2 | 2 | 0 |
| 2026-05 | 2 | 1 | 1 |
| 2026-06 | 2 | 1 | 1 |
+---------+--------+-----------+---------------+-- Hard
SELECT customer_id,
COUNT(*) AS orders,
SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
ROUND(AVG(CASE WHEN status = 'delivered' THEN 1.0 ELSE 0 END) * 100, 1) AS delivered_rate,
strftime('%Y-%m', MAX(order_date)) AS last_order_month,
CASE
WHEN COUNT(*) >= 3 THEN 'loyal'
WHEN COUNT(*) = 2 THEN 'returning'
ELSE 'new'
END AS segment
FROM orders
GROUP BY customer_id
ORDER BY orders DESC, customer_id;+-------------+--------+-----------+----------------+------------------+-----------+
| customer_id | orders | delivered | delivered_rate | last_order_month | segment |
+-------------+--------+-----------+----------------+------------------+-----------+
| 1 | 3 | 3 | 100.0 | 2026-04 | loyal |
| 2 | 3 | 2 | 66.7 | 2026-05 | loyal |
| 3 | 3 | 3 | 100.0 | 2026-06 | loyal |
| 6 | 2 | 1 | 50.0 | 2026-06 | returning |
| 4 | 1 | 0 | 0.0 | 2026-02 | new |
| 5 | 1 | 1 | 100.0 | 2026-03 | new |
| 7 | 1 | 1 | 100.0 | 2026-05 | new |
+-------------+--------+-----------+----------------+------------------+-----------+Summary
CASE WHEN … THEN … ELSE … ENDreturns the value of the first true condition; withoutELSEthe rest is NULL, and NULL inputs fall through toELSE. The simple formCASE col WHEN value …compares one expression.- Group by a
CASEto bucket values; sort buckets withMIN(…)or aCASEinORDER BY. - Conditional aggregation (
SUM(CASE …),COUNT(CASE …),FILTER, MySQL'sSUM(condition)) builds pivots and rates in one pass. - Group by
strftime('%Y-%m', date)(orTO_CHAR/DATE_FORMAT) for monthly figures;ROLLUPadds totals on the servers. HAVING COUNT(*) > 1finds duplicates (normalise text first); groups built from one table cannot show what is missing, which is the job of joins.
Next: Joins: INNER JOIN and Table Aliases. Every query so far used one table, which is why product names, order statuses and empty categories kept escaping us. Joins combine tables, so you can finally report revenue per product name, per customer and per category.