Solve “Deduplicate events with ROW_NUMBER”

Free. Runs in your browser, no install.

SQL · Aggregations & Grouping

SUM(CASE WHEN...) and conditional aggregation

Mediumconditional aggregationcase whensum

Interview question

How does SUM(CASE WHEN ...) work for conditional aggregation?

How to answer

The short answer

SUM(CASE WHEN...) is a common form of conditional aggregation.

Example:

SQL
SELECT
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END)
FROM orders;

This counts completed orders.

You can also calculate amounts:

SQL
SUM(
    CASE
        WHEN status = 'completed'
        THEN amount
        ELSE 0
    END
)

Same idea either way: aggregate only the rows that match a condition.

In short

> Conditional aggregation means applying an aggregate function only to rows that satisfy a condition.

Lock it in with a real drill

You remember an answer longer once you have used it. Try a hands-on problem on this concept.

Open related drill