SUM and AVG — Totals and Averages
The shop's owner wants two numbers on her dashboard: the total number of items sold, and the average quantity per order. Both come from one column, quantity, and both are one line of SQL. The subtle part is what these functions do with missing values and with empty results.
The rule
SUM adds and AVG averages the non-NULL values; NULLs are skipped, not zero.
Using the orders table:
| order_id | customer_id | product_id | quantity | status |
|---|---|---|---|---|
| 101 | 1 | 1 | 2 | shipped |
| 102 | 2 | 3 | 1 | pending |
| 103 | 1 | 2 | 5 | shipped |
| 104 | 3 | 1 | 1 | cancelled |
| 105 | 3 | 2 | 3 | shipped |
SELECT SUM(quantity) FROM orders; -- 2 + 1 + 5 + 1 + 3 = 12
SELECT AVG(quantity) FROM orders; -- 12 / 5 = 2.4
SELECT SUM(quantity) FROM orders WHERE status = 'shipped'; -- 2 + 5 + 3 = 10Both work only on numbers. Three details catch people out:
- NULLs are ignored. The average of 10, NULL and 20 is 15 (30 ÷ 2), not 10 (30 ÷ 3). If a missing value should count as zero, say so:
AVG(COALESCE(rating, 0)). - An empty set gives NULL.
SUMover zero rows is NULL, not 0. WriteCOALESCE(SUM(quantity), 0)when a report needs a number. - Integer averages can be truncated. SQL Server returns an integer from
AVGof an integer column, soAVG(quantity)above gives 2 there. Cast first —AVG(CAST(quantity AS DECIMAL(10, 2)))— when you need the fraction. PostgreSQL and MySQL return a decimal.
You can also aggregate an expression, not just a column. SUM(quantity * 60) would total the value of orders if every product cost 60.
The shortcut
Before trusting a SUM or AVG, ask "what about NULLs and empties?" in two quick checks:
- Could the column be NULL? Then AVG divides by fewer rows than you think.
- Could the WHERE match nothing? Then SUM gives NULL — wrap it in COALESCE.
The data
Five reviews with ratings 4, 5, NULL, 3, NULL.
Drills
Answer fast — trust the rule.
A column holds 10, NULL and 20. What is AVG of it?
Using the orders table, what does SELECT SUM(quantity) FROM orders WHERE customer_id = 1; return?
SELECT SUM(quantity) FROM orders WHERE status = 'returned'; matches no rows. What is returned?
In SQL Server, AVG(quantity) over the orders table returns 2 instead of 2.4. Why?
Remember
- SUM adds and AVG averages the non-NULL values; NULLs are skipped, not zero.
- Two checks: NULLs in the column, and empty results.
- COALESCE turns NULL into the number a report expects.