Aggregate functions behave predictably only when every group has normal values. Test the awkward groups: one with no rows, one with a NULL, and one with only NULLs.
This lesson belongs to SQL reasoning in a fictional database. If SUM and GROUP BY are new, read using aggregation and filtering first.
What should you test besides the normal case?
List four cases before you trust an aggregate query: a normal group, a group with no rows, a group with a NULL among real values, and a group with only NULLs. Most wrong answers come from the last three.
Here is an original sports day database. House has four rows, and Points records what each house earned.
| HouseID | HouseName |
|---|---|
| H1 | Red |
| H2 | Blue |
| H3 | Green |
| H4 | Yellow |
| PointID | HouseID | Points |
|---|---|---|
| 1 | H1 | 10 |
| 2 | H1 | NULL |
| 3 | H2 | 15 |
| 4 | H2 | 5 |
| 5 | H3 | 8 |
Worked example: an inner join hides Yellow
The query SELECT HouseName, SUM(Points) FROM House INNER JOIN Points ON House.HouseID = Points.HouseID GROUP BY HouseName gives three rows.
- Red: 10, because SUM skips the NULL.
- Blue: 15 + 5 = 20.
- Green: 8.
Yellow is absent. It has no Points rows, so the inner join dropped it before grouping. A scoreboard that omits Yellow is wrong, even though the query ran.
Changing INNER JOIN to LEFT JOIN keeps Yellow in the result with SUM equal to NULL.
COUNT(*) and COUNT(column) are not the same
For Red, COUNT(*) is 2 because two rows exist. COUNT(Points) is 1 because one of those values is NULL.
The average follows the same rule. AVG(Points) for Red is 10 ÷ 1 = 10. A student who assumes NULL means zero would write (10 + 0) ÷ 2 = 5.
| Group | COUNT(*) | COUNT(Points) | SUM | AVG |
|---|---|---|---|---|
| Red | 2 | 1 | 10 | 10 |
| Blue | 2 | 2 | 20 | 10 |
| Yellow, after LEFT JOIN | 1 | 0 | NULL | NULL |
Yellow shows the trap. After the LEFT JOIN, Yellow has one joined row full of NULLs, so COUNT(*) reports 1. Use COUNT(PointID) to count real point entries, which gives 0.
A test checklist you can reuse
- Name the table that lists every group, and join from it with LEFT JOIN.
- Decide what the question wants for an empty group: NULL, 0 or omitted.
- Choose COUNT(*) only when you want to count joined rows, and COUNT(column) for real entries.
- Write one test row for each awkward case, then predict every output by hand.
- Run the query and compare.
The restricted pseudocode trace trainer follows the same predict-then-check habit for programs.
Check yourself
A new row is added to Points: (6, H4, NULL). Using a LEFT JOIN from House, what are SUM(Points), AVG(Points), COUNT(*) and COUNT(Points) for Yellow?
Answer
Yellow now has exactly one Points row, and its value is NULL.
SUM(Points) is NULL, because there are no values to add. AVG(Points) is NULL for the same reason. COUNT(*) is 1, because one row exists. COUNT(Points) is 0, because the only value is NULL.
What to study next
Put all four skills together in the cluster practice set. If row counts after a join still feel uncertain, revisit why a join returns more rows than expected.
If you want a teacher to give you awkward test rows, see online one-to-one Computer Science tuition.