Lessons · SQL · adding up only some rows

Two totals from one pass

SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) adds only the paid rows. Put several such sums in one SELECT and a status becomes columns.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Paid and refunded side by side, successes and failures per job, credits and debits per account: the pivot every report wants, without a second query per column.

How to think about it

One CASE per column, ELSE 0 so the row adds nothing to the other columns, then GROUP BY the entity. COUNT works the same way with ELSE NULL, since COUNT ignores NULL.

Worked example

SELECT customer_id, SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid, SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded

Each row adds to exactly one column.

FROM orders GROUP BY customer_id;

One row per customer, two totals.

Your turn

Count the five-star reviews per film alongside all reviews.

SELECT film_id, COUNT(*) AS all_reviews, SUM(CASE WHEN stars = 5 THEN 1 ELSE  END) AS five_star FROM reviews GROUP BY film_id;

The trap

ELSE NULL inside SUM gives NULL for a group with no matching rows, not 0. Use ELSE 0, or COALESCE the result.

Practise adding up only some rows on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.