In this article
Before you start
Why interviewers ask this
A common screen asks for "total paid, total refunded and total cancelled per region in one query". A weak answer writes three queries and joins them. A strong answer writes one query with three conditional sums, then explains what happens for a region with no cancelled rows. The question separates people who memorised the syntax from people who know how sum treats NULL, which is a common source of wrong totals. It is a staple of SQL interviews and appears in analytics and data engineering interviews because the same pattern builds the columns of many dashboard tables. Practise saying the pattern in one sentence: test the row, keep the value or NULL, sum, group, and guard the empty case.
SUM and CASE WHEN from first principles
- Conditional aggregation
- Conditional aggregation is a query technique that places a test inside an aggregate function so that the function adds, counts or averages only the rows that pass the test, which lets one query return several filtered totals side by side.
- Case expression
- A case expression is an SQL expression that checks conditions in order and returns the result of the first one that is true, or the else result, or NULL when there is no else and no condition is true.
In plain words
Picture a cashier sorting receipts at the close of a shift. Each receipt goes into one of three trays: paid, refunded or cancelled. A conditional sum adds one tray at a time. It writes each amount only in its own tray's column and leaves the other columns blank. The analogy breaks in one place. A blank in SQL is NULL, which means "unknown", and the adding machine skips it. A tray with no receipts therefore shows no total at all, not zero.
In one precise sentence: sum(case when condition then value end) tests every row, replaces the value with NULL where the test is not true, and sums what is left. The examples use a ten-row table of orders with a region, a status (paid, refunded or cancelled) and an amount. The table is illustrative, and every result was run in SQLite.1
Step 1: Turn each row into a value with CASE WHEN
A case expression decides, row by row, what value that row contributes. case when status = 'paid' then amount else 0 end returns the amount for a paid row and 0 for any other row. The PostgreSQL documentation says the conditions are tested in order, the first true one wins, and, when none is true, the result is that of the else clause.
The input is one row. The output is one value for that row, so the table keeps its ten rows and gains a temporary column. The same page says a case clause can be used wherever an expression is valid, which is why the expression can sit inside an aggregate function. Nothing has been added up yet.
Step 2: Let SUM skip the NULLs
Sum adds the non-NULL values it receives and ignores the rest. The PostgreSQL manual says most aggregate functions "ignore null inputs", and the SQLite aggregate-function page says "The sum() and total() aggregate functions return the sum of all non-NULL values in the group." Skipping NULL is the engine of the whole pattern.
The consequence is a choice about else. With else 0, non-matching rows add zero. Without an else, a case expression returns NULL when no condition is true, a rule stated on the PostgreSQL page and on the SQLite expression page. Sum skips those rows. For one group both forms give the same total, 250 for the paid North rows in our table.1 Because amount is never NULL in this table, they differ only when a group has rows but none of them matches, as South does: without an else the sum is NULL, with else 0 it is 0. The next step covers that case and the case of no rows at all.
Step 3: Know what SUM returns when no row matches
The sum of no matching values is NULL, not zero. The PostgreSQL manual states it directly: "sum of no rows returns null, not zero as one might expect" (PostgreSQL 18, aggregate functions). The SQLite aggregate-function page explains the same rule, adds that "the SQL standard requires it", and offers a non-standard total() function that returns 0.0 instead.
In our table the South region has two rows and neither is paid. sum(case when status = 'paid' then amount end) returns NULL for South, while the same expression wrapped in coalesce(..., 0) returns 0.1 A NULL cell and a 0 cell read differently, and NULL plus a number is NULL. The guard that always works is coalesce, a function that returns its first non-NULL argument.
An else 0 inside the case expression gives zero only when at least one row reaches the sum: over no rows at all (for example a where that removes everything), sum(case when status = 'paid' then 1 else 0 end) still returns NULL, while the matching count returns 0.1 So use coalesce(sum(...), 0) when a zero is required.
Step 4: Add GROUP BY to get one conditional total per group
Group by splits the rows into groups, and each aggregate is computed once per group. Put several conditional sums in one select and you get several filtered totals side by side from a single scan of the table. For our orders, group by region with a total, a paid sum and a paid-order count returns one row per region.
The input is the table and the grouping column. The output is a small table with one row per group and one column per conditional sum. This is often called a pivot, because the statuses that were stored down a column now run across as columns. The tray analogy holds: each column is one tray, and every group keeps its own set of trays. The next step covers counting with the same trick.
Step 5: Count with the same pattern, and avoid the COUNT trap
sum(case when condition then 1 else 0 end) counts the rows that meet the condition. count(case when condition then 1 end) does too, because count of an expression counts only its non-NULL values, a rule shown in the PostgreSQL expressions page.
The trap is mixing the two. count(case when condition then 1 else 0 end) counts every row, because 0 is not NULL. In our run that form returned 10 for the ten orders, while the two correct forms returned 4 paid orders.1 The input is the same test. The output differs only by whether non-matching rows become NULL or 0. Choose sum with else 0, or count with no else, and never count with else 0.
Step-by-step description
- Case when returns a value or NULL for each row.
- Sum skips every NULL.
- No matching value gives NULL; coalesce makes it 0.
- Group by runs each aggregate once per group.
- Count with else 0 counts all rows.
- The sum of no rows is NULL, not zero; coalesce(sum(...), 0) always gives zero.
- Count with else 0 counts every row, because 0 is not NULL.
Knowing it is half the job. Saying it is the other half.
Interview Clozo is an AI interview copilot for real interview rounds: it hears the question and puts a strong answer on your screen, and you say it in your own words. See the features or how it works.
Start Free Trial →Worked example
The query below returns one row per region with the total, the paid amount and the number of paid orders. It ran on the ten-row illustrative table, and every figure comes from the saved script.1
SELECT region,
SUM(amount) AS total,
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid,
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders
FROM orders
GROUP BY region;| Region | Total amount | Paid amount | Paid orders |
|---|---|---|---|
| East | 310 | 200 | 2 |
| North | 340 | 250 | 2 |
| South | 115 | 0 | 0 |
Check one row by hand. North has paid orders of 200 and 50, which sum to 250. Its other two rows are a cancelled 60 and a refunded 30, so the total is 340. A second query used three more conditional sums over the whole table and returned paid, refunded and cancelled amounts of 450, 95 and 220 in one scan, which add up to the table total of 765.1
The where alternative gives a different answer. select region, sum(amount) from orders where status = 'paid' group by region returned only East and North, because the filter removed every South row before grouping, so South vanished instead of showing 0.1
Text description of the diagram
- North has a total of 340 and a paid amount of 250.
- South has no paid rows and still shows a paid amount of 0.
Trade-offs and edge cases
Put the case expression inside the aggregate when you need several filtered totals at once or must keep every group. Use where when one filter applies to the whole query and groups with no match may disappear. The table below compares the four forms, and the chart further down shows the count trap.
The filter clause is an aggregate modifier that feeds only the rows passing a test into the function. The PostgreSQL manual says "only the input rows for which the filter_clause evaluates to true are fed to the aggregate function" (PostgreSQL 18, expressions), and the SQLite aggregate-function page documents the same clause; both engines support it as of October 2026. In our run sum(amount) filter (where status = 'paid') matched the case form row for row, including NULL for South.1 It reads more clearly, but other engines and older versions may lack it, so the case form remains the portable choice.
A second edge case is types: the results of a case expression must convert to one output type, so mixing a number and a text label in one expression fails or converts silently, depending on the engine. A third is integer overflow, where the total grows past the largest integer the type can hold: SQLite's sum() raises an error if integer inputs overflow, while total() does not. A fourth is a NULL amount: a matching row whose amount is NULL adds nothing, so a group whose only matching rows have NULL amounts still returns NULL when there is no else.
| Form | Group with no match | Notes |
|---|---|---|
sum(case ... end) | NULL | portable, several totals per query |
sum(case ... else 0 end) | 0 | portable, no NULL cells |
sum(...) filter (where ...) | NULL | clearer, engine support varies |
where status = 'paid' | group missing | one filter for everything |
Text description of the diagram
- Count with else 0 returned 10 for ten orders.
- Count with no else and sum with else 0 both returned 4 paid orders.
How to answer it in an interview
How would you get total paid and total refunded amounts per region in one query?
I would group by region and put a case expression inside each sum. For paid, I sum the amount when the status is paid and add zero otherwise, and I do the same for refunded. The case expression returns the amount only for matching rows, and sum adds them, so one pass gives both columns. If I leave out the else, non-matching rows become NULL, which sum skips, but a region with no matches then returns NULL instead of zero, so I either add else zero or wrap the sum in coalesce. I would not use where, because it drops regions with no match.
The spoken structure is: the shape of the query, why it works, then the guard for the empty group.
Common mistakes
- Treating a missing total as zero. (wrong) "The sum of no matches is 0." (right) "The sum of no values is NULL, so I wrap it in coalesce when the report needs a zero; else 0 alone is not enough when no row reaches the sum."
- Counting with else 0. (wrong)
count(case when status = 'paid' then 1 else 0 end), which counted all 10 rows in our table. (right) "Use sum withelse 0, or count with no else." - Filtering with where when every group must appear. (wrong) "I put the status test in where." (right) "Where removes the rows before grouping; a conditional sum keeps the group and shows 0."
- Writing a separate query per status. (wrong) "I run three queries and join them." (right) "One query with three conditional sums scans the table once."
- Forgetting that NULL plus a number is NULL. (wrong) "I add the paid and refunded columns directly." (right) "I guard each column with coalesce before I add them."
Follow-up questions
What does SUM(CASE WHEN ... THEN 1 ELSE 0 END) do?
It counts the rows that meet the condition: each matching row contributes 1, every other row contributes 0, and sum adds them. In our table it returned 4 for paid orders.1
What is the difference between COUNT(CASE WHEN ...) and SUM(CASE WHEN ...)?
Count tallies non-NULL values, so within a group count(case when c then 1 end) equals sum(case when c then 1 else 0 end). Over no rows, count gives 0 and sum gives NULL (SQLite 3.51.1 returned 0 and NULL;1 PostgreSQL says "except for count, these functions return a null value when no rows are selected"). Add else 0 to the count version and it tallies every row.
Why does SUM(CASE WHEN ...) return NULL instead of 0?
The standard defines the sum of no values as NULL, and a case expression with no matching row yields only NULLs. Wrap it as coalesce(sum(...), 0) to get zero; an else 0 does that only when at least one row reaches the sum.
Can I use CASE WHEN inside a WHERE clause instead?
A case expression can be a condition in where, but it then filters rows for the whole query, so groups with no match disappear. The PostgreSQL manual shows one used in where to guard a division by zero.
Is there a cleaner way than SUM and CASE WHEN?
Where the engine supports it, the filter clause says the same thing more plainly. For sorting the result, see sorting with order by descending; for combining tables first, see the SQL join diagram guide. More practice is on the database interview questions page, and data modeling interview questions covers the tables behind such reports.
- A conditional sum puts case when inside sum, so only rows that pass the test add up, because other rows become NULL and sum skips NULL.
- Else 0 gives zero only when some row reaches the sum, so coalesce(sum(...), 0) is the guard that always works, because the sum of no values is NULL.
- Count with sum and else 0, or with count and no else, never count with else 0.
- A conditional sum keeps every group; where can remove groups that have no match.
Sources
- PostgreSQL 18 documentation, 9.18 Conditional Expressions (CASE) — PostgreSQL Global Development Group (page shows no date; documentation version 18.6, read 2026-10-24), 24 Oct 2026
- PostgreSQL 18 documentation, 9.21 Aggregate Functions — PostgreSQL Global Development Group (page shows no date; documentation version 18.6, read 2026-10-24), 24 Oct 2026
- PostgreSQL 18 documentation, 4.2 Value Expressions (aggregate expressions and FILTER) — PostgreSQL Global Development Group (page shows no date; documentation version 18.6, read 2026-10-24), 24 Oct 2026
- SQLite: Built-in Aggregate Functions — SQLite Consortium (page shows no date; read 2026-10-24), 24 Oct 2026
- SQLite: Expressions (the CASE expression) — SQLite Consortium (page shows no date; read 2026-10-24), 24 Oct 2026
