Lessons · SQL · adding a column up
Adding a column up
SUM(amount) adds the values in a column across the rows the query keeps. NULLs are skipped; if no rows remain, the result is NULL, not 0.
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
Revenue this month, stock on hand, minutes of film per director: totals are the whole point of most reports.
How to think about it
Filter first with WHERE, then SUM what is left. Wrap it in COALESCE(SUM(x), 0) when an empty set should read as zero on a dashboard.
Worked example
SELECT SUM(amount) FROM ordersThe total.
WHERE status = 'paid';Of the paid ones only.
SELECT COALESCE(SUM(amount), 0) FROM orders WHERE status = 'never';No rows: SUM is NULL, COALESCE turns it into 0.
Your turn
Total stock across products.
SELECT (stock) FROM products;
Run a query against real tables
The trap
SUM over no rows is NULL. Code that adds that result to something else gets NULL back and shows a blank where a zero was expected.
Practise adding a column up on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.