Conditional Aggregation with SUM(CASE WHEN ...)

• Conditional aggregation – applies an aggregate function over a CASE expression so that only rows meeting a condition contribute to the result.
• SUM(CASE WHEN ... THEN 1 ELSE 0 END) – counts matching rows per group because each match contributes 1 and each non-match contributes 0.
• COUNT(CASE WHEN ... THEN 1 END) – achieves the same result because non-matching rows produce NULL, which COUNT ignores.
• HAVING with multiple conditionals – tests combined conditions like "has both completed and cancelled" in one grouped pass.
• Pivot use case – SUM(CASE WHEN channel='ATM' THEN amount END) turns each channel into its own column.

What is conditional aggregation?

Conditional aggregation means pushing a condition inside an aggregate function using a CASE expression. Instead of counting or summing all rows in a group, you count or sum only the rows that match a specific condition. This technique lets you compute multiple conditional measures in a single grouped pass over the table.

-- Sample data at National Bank of India
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status      VARCHAR(20) NOT NULL,
    amount      DECIMAL(15,2) NOT NULL,
    channel     VARCHAR(20) NOT NULL
);

INSERT INTO orders (customer_id, status, amount, channel) VALUES
(1001, 'COMPLETED', 45000.00, 'UPI'),
(1001, 'CANCELLED', 12000.00, 'ATM'),
(1002, 'COMPLETED', 78000.00, 'NEFT'),
(1002, 'COMPLETED', 32000.00, 'UPI'),
(1003, 'CANCELLED', 15000.00, 'ATM');

How does SUM(CASE WHEN ...) work?

The CASE expression evaluates for every row in the group. If the condition is true, it returns 1 (or the amount); if false, it returns 0. Summing those values gives you a conditional count or conditional total per group.

-- Count completed and cancelled orders per customer
SELECT customer_id,
       SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) AS completed_count,
       SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END) AS cancelled_count
FROM   orders
GROUP BY customer_id;

 customer_id | completed_count | cancelled_count
-------------+-----------------+----------------
        1001 |               1 |               1
        1002 |               2 |               0
        1003 |               0 |               1

How is COUNT(CASE WHEN ...) different from SUM(CASE WHEN ...)?

With COUNT, the CASE expression returns 1 for matches and NULL (no ELSE clause) for non-matches. Since COUNT ignores NULLs, only matching rows are counted. The result is the same as SUM with 1/0, but the intent is expressed differently.

-- Using COUNT: CASE returns NULL for non-matches, COUNT ignores NULLs
SELECT customer_id,
       COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) AS completed_count,
       COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END) AS cancelled_count
FROM   orders
GROUP BY customer_id;

-- WRONG: ELSE 0 with COUNT counts ALL rows (0 is not NULL)
SELECT customer_id,
       COUNT(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) AS wrong_count
FROM   orders
GROUP BY customer_id;
-- wrong_count equals total rows, not just completed ones

How do you find customers with BOTH a completed and a cancelled order?

Group by customer and use conditional aggregation in the HAVING clause to require both counts above zero. This reads the table once.

SELECT customer_id
FROM   orders
GROUP BY customer_id
HAVING SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END) > 0;

 customer_id
-------------
        1001

Only Rajesh (1001) has both. The alternative without conditional aggregation would be a self-join or two EXISTS subqueries, both of which scan the table multiple times.

How is this different from a self-join approach?

| Aspect | Conditional Aggregation | Self-Join |
|---|---|---|
| Table scans | One | Two (or more) |
| Duplicates | No | May need DISTINCT |
| Extensibility | Add more CASE expressions | Add more joins |
| Readability | Clean and compact | Gets verbose quickly |
| Performance | Single group pass | Multiple join passes |

-- Self-join approach (less efficient)
SELECT DISTINCT a.customer_id
FROM   orders a
JOIN   orders b ON a.customer_id = b.customer_id
WHERE  a.status = 'COMPLETED'
  AND  b.status = 'CANCELLED';

How do you use conditional aggregation for pivoting?

Conditional aggregation turns row values into columns. Each distinct value becomes its own CASE expression, producing a crosstab report.

-- Pivot: total amount per channel, per customer
SELECT customer_id,
       SUM(CASE WHEN channel = 'UPI'  THEN amount ELSE 0 END) AS upi_total,
       SUM(CASE WHEN channel = 'ATM'  THEN amount ELSE 0 END) AS atm_total,
       SUM(CASE WHEN channel = 'NEFT' THEN amount ELSE 0 END) AS neft_total
FROM   orders
GROUP BY customer_id
ORDER BY customer_id;

 customer_id | upi_total  | atm_total  | neft_total
-------------+------------+------------+-----------
        1001 |   45000.00 |   12000.00 |       0.00
        1002 |   32000.00 |       0.00 |   78000.00
        1003 |       0.00 |   15000.00 |       0.00

What is the MySQL shorthand?

In MySQL, a boolean expression evaluates to 1 (true) or 0 (false), so you can write SUM(status = 'COMPLETED') as a shorthand for SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END). This does not work in PostgreSQL or SQL Server.

-- MySQL shorthand
SELECT customer_id,
       SUM(status = 'COMPLETED') AS completed_count,
       SUM(status = 'CANCELLED') AS cancelled_count
FROM   orders
GROUP BY customer_id;

What about PostgreSQL's FILTER clause?

PostgreSQL supports the FILTER (WHERE ...) clause, which is the modern standard-SQL way to express the same intent. It reads more clearly than the CASE expression.

-- PostgreSQL FILTER clause
SELECT customer_id,
       COUNT(*) FILTER (WHERE status = 'COMPLETED') AS completed_count,
       COUNT(*) FILTER (WHERE status = 'CANCELLED') AS cancelled_count
FROM   orders
GROUP BY customer_id
HAVING COUNT(*) FILTER (WHERE status = 'COMPLETED') > 0
   AND COUNT(*) FILTER (WHERE status = 'CANCELLED') > 0;

What should a senior engineer know?

• Prefer the explicit SUM(CASE WHEN ... THEN 1 ELSE 0 END) form over tricks so the intent is unmistakable to other developers.
• For pure existence tests ("does at least one exist?"), MAX(CASE WHEN cond THEN 1 ELSE 0 END) can be marginally cheaper than a full SUM because MAX short-circuits after finding one match.
• The column set in a pivot is fixed at query-write time. If a new channel appears, you must add a new CASE expression (or use dynamic SQL).
• An index on (customer_id, status) supports the grouping and the conditional filters.
• Conditional aggregation replaces not just self-joins but also multiple EXISTS subqueries, INTERSECT operations, and UNION-based approaches.

What are common interview follow-up questions?

• How do you find customers with state A but NOT state B? (HAVING COUNT(... A ...) > 0 AND COUNT(... B ...) = 0.)
• How do you count how many distinct statuses each customer has? (COUNT(DISTINCT status).)
• Can you use conditional aggregation with AVG? (Yes: AVG(CASE WHEN channel = 'UPI' THEN amount END) averages only UPI amounts, ignoring NULLs.)