SUM
Total a set of values — ignoring NULLs, and only as trustworthy as the grain you grouped by.
Syntax
SUM(expression)| Parameter | Type | Notes |
|---|---|---|
| expression* | numeric | The values to add. |
What it does
SUM() adds values across the rows in a group.
SQL playground
Loading editor…
⌘/Ctrl + Enter
NULLs are skipped, not zeroed
SQL playground
Loading editor…
⌘/Ctrl + Enter
If every value in the group is NULL, SUM() returns NULL, not 0 — wrap it
in COALESCE(SUM(x), 0) if a numeric zero is what a downstream chart or diff
expects.
Summing a subset
Same CASE-inside-aggregate trick as COUNT:
SQL playground
Loading editor…
⌘/Ctrl + Enter
Use ELSE 0 here (not the default NULL) — SUM skips NULLs, so the wrong
ELSE would just as quietly do the right thing, but 0 makes the intent
explicit. See Pivot without PIVOT for the full pattern.
Gotchas
- Fan-out inflates sums silently. If a join duplicates rows before the
SUM, the total is wrong with no error — see INNER JOIN: fan-out. - The grain has to be intentional.
SUM()collapses rows into whateverGROUP BY(or the whole result, with none) says the grain is. - Row counts and sums can disagree if a
WHEREfilter excludes rows before aggregation but a report elsewhere doesn't apply the same filter.
- AVG — SUM divided by a NULL-aware COUNT.
- Running totals — SUM as a window function instead of a collapsing aggregate.
- GROUP BY — the grain SUM is computed over.
Safety checklist
- confirm the intended grouping before aggregating
- decide whether NULL should be treated as zero or excluded
- validate the total against expected business numbers, especially after a join
Was this page helpful?