Category-Level Metrics with CASE-Based Conditional Aggregation

Hello! In the previous lesson, you used WHERE to choose which sales enter a calculation and HAVING to choose which category summaries remain. Now suppose you need both web and store metrics in the same category summary. Filtering to web sales would make the store sales disappear before you could measure them.

This lesson introduces conditional aggregation: using CASE inside aggregate functions to calculate several metrics from the same grouped rows. You’ll produce counts, totals, and an average while keeping the result at one row per category.

---

Make a decision for each row

We’ll use the same sales table as before, with one additional sale. Each row represents one sale:

sale_id	category	channel	amount
101	books	web	20
102	books	web	30
103	books	store	10
104	games	web	40
105	games	store	15
106	games	store	NULL
107	games	web	5
108	books	store	25
109	music	web	12

The new music sale will help us see what happens when a category has no store sales.

A CASE expression chooses a value based on a condition. For example:

CASE WHEN channel = 'web' THEN 1 ELSE 0 END

This expression produces 1 for a web sale and 0 for any other sale. It does not remove rows. Within each category, SUM can add those values to count web sales. To count store sales at the same time, use a second CASE expression with channel = 'store'.

You can also return an amount rather than a flag:

CASE WHEN channel = 'web' THEN amount ELSE 0 END

For sale 101, these two expressions return 1 and 20. For store sale 105, they return 0 and 0. Each CASE ends with END; its ELSE supplies the value when the WHEN condition is not true.

Watch how DataCamp places this row-level decision inside an aggregate. Its football example uses different conditions, but the SQL pattern is the one we need for sales channels.

---

Put several metrics in one category summary

The requested result grain is still one row per category. GROUP BY category establishes that grain; each aggregate in SELECT calculates a different metric over the category’s sales.

SELECT
    category,
    COUNT(*) AS sale_rows,
    SUM(CASE WHEN channel = 'web' THEN 1 ELSE 0 END) AS web_sales,
    SUM(CASE WHEN channel = 'store' THEN 1 ELSE 0 END) AS store_sales,
    SUM(amount) AS total_amount,
    SUM(CASE WHEN channel = 'web' THEN amount ELSE 0 END) AS web_amount,
    SUM(CASE WHEN channel = 'store' THEN amount ELSE 0 END) AS store_amount
FROM sales
GROUP BY category
ORDER BY category;

category	sale_rows	web_sales	store_sales	total_amount	web_amount	store_amount
books	4	2	2	85	50	35
games	4	2	2	60	45	15
music	1	1	0	12	12	0

Read the games row carefully. Its two web sales contribute 40 and 5 to web_amount, for 45. It also has two store rows, so store_sales is 2. But one store sale has a NULL amount; SUM ignores that unknown amount and adds the known 15. Thus store_amount is 15, not a count of store rows.

The music row shows why the ELSE 0 matters. Its sale contributes 0 to the store flag and 0 to the store amount. The category remains in the result with zero store metrics.

Notice what the query does not contain: WHERE channel = 'web'. That filter would remove store rows before any of these metrics were calculated. A CASE inside an aggregate instead controls what each row contributes to that particular metric. One sale can contribute to sale_rows and total_amount, to its own channel’s metrics, and zero to the other channel’s metrics.

For a closer look at the 1/0 counting technique, watch the following example. It builds a count of qualifying orders alongside a count of all orders—the same relationship as web_sales and sale_rows above.

---

Choose the right value for the aggregate

The general pattern is to let CASE determine each row’s contribution and let the aggregate combine those contributions within each category. The values returned by CASE must match the metric you want.

For a conditional row count, summing 1 for a match and 0 otherwise is direct:

SUM(CASE WHEN channel = 'web' THEN 1 ELSE 0 END)

You may also encounter this equivalent count for categories that have source rows:

COUNT(CASE WHEN channel = 'web' THEN 1 END)

Here, a non-web row gets NULL because there is no ELSE, and COUNT(expression) counts only non-NULL results. Do not add ELSE 0 to this COUNT expression: zero is a non-NULL value, so COUNT would count every sale in the category. By contrast, SUM needs the explicit zeros to give an intuitive zero count when no rows match.

For a conditional amount total, return amount for a match:

SUM(CASE WHEN channel = 'store' THEN amount ELSE 0 END)

If you omit ELSE 0, nonmatching rows produce NULL. For music, which has no store matches, SUM(CASE WHEN channel = 'store' THEN amount END) would return NULL rather than 0. The ELSE 0 version expresses the intended report value for a category that has sales but no store sales.

There is a separate distinction when an amount itself is NULL. In games, the store sale with a missing amount still counts as a store sale, but it adds no known amount to store_amount. Counts of rows and sums of amounts answer different questions; they need not move together. You’ll examine NULL behavior more fully later in the course.

The same idea works for a conditional average, but the choice of ELSE changes. To find the average web-sale amount per category, use:

AVG(CASE WHEN channel = 'web' THEN amount END) AS avg_web_amount

This gives books 25, games 22.5, and music 12. Non-web rows produce NULL and are excluded from the average. If you wrote ELSE 0, store sales would enter the average as zeros, changing the question to an average across all category rows of an amount that is zero for store sales.

---

Keep the question and the grain in view

Before writing a report with many metrics, state its grain: one row per what? Here it is one row per category. Then define each output column in words: “number of all sales,” “number of web sales,” “sum of known store amounts,” and so on. That makes it easier to decide whether each CASE should return 1, amount, 0, or NULL.

You can still use the filtering rules from the previous lesson. For example, if you wanted to display only categories with at least 50 in web sales, you could add a group condition:

HAVING SUM(CASE WHEN channel = 'web' THEN amount ELSE 0 END) >= 50

Books would remain, with both its web and store metrics visible. A WHERE channel = 'web' clause would instead remove the store sales from the calculation. The difference is not merely which rows appear in the final output; it is which rows are available to each aggregate.

As a quick validation of the displayed result, web_sales + store_sales equals sale_rows for each category in this table, where every sale uses one of those two channels. If another channel were possible, that equality would no longer be expected. Validation checks are useful only when their assumptions match the data.

---

Takeaway

GROUP BY sets the output grain; CASE inside an aggregate decides what each source row contributes to a particular metric. Use SUM(CASE ... THEN 1 ELSE 0 END) for conditional row counts, return a value such as amount for conditional totals, and be deliberate about whether nonmatching rows should contribute 0 or be excluded as NULL.

You can now produce a compact category report with several metrics without filtering away the rows needed by other metrics. Next, the course moves to combining tables with joins, where preserving the intended rows—and the intended grain—becomes even more important.