SUM Function
SQL · Aggregate Functions
Theory
SUM adds up the values of a numeric column across a set of rows and returns one total. Without GROUP BY it totals the whole table (after WHERE has filtered it). With GROUP BY it produces one total per group, for example total salary per department.
SUM simply ignores NULL values: a NULL is not treated as 0, it is just left out. If every value in the group is NULL, or the group has no rows, SUM returns NULL rather than 0, which surprises many beginners. Wrap it as COALESCE(SUM(col), 0) when you want a 0. You can also sum an expression, such as SUM(quantity * price), or a condition, such as SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) to total only paid orders.
Why we use it
Totals are the backbone of business reporting: revenue, cost, salary bill, units sold. SUM lets the database add millions of rows in one pass and hand back just the numbers you need, per group if you like.
When to use it
Use SUM for additive measures like money, quantity and hours. Do not sum things that are not additive, such as percentages, averages or account balances taken at different dates. For running totals that keep every row, use SUM() OVER instead.
How it works
- Rows arrive after FROM and WHERE
- GROUP BY splits rows into groups (or one group if absent)
- Read the next value in the group
- Is the value NULL? Yes: Skip it. No: Add it to the running total.
- Any non-NULL value in the group? Yes: Return the total. No: Return NULL (use COALESCE for 0).
- One total per group
Syntax
SUM(column)Example
SELECT SUM(salary) FROM employees;Key points
- Ignores NULL values (they are not treated as 0)
- Returns NULL, not 0, when there are no non-NULL values
- Only works on numeric columns or expressions
- SUM(CASE WHEN ... THEN x ELSE 0 END) gives a conditional total
- SUM(DISTINCT col) adds each distinct value once, rarely what you want