On this page
Syntax
SUM(expression)SUM(DISTINCT expression)SUM(expression) OVER (...) | Parameter | Type | Required | Description |
|---|---|---|---|
expression |
numeric column or expression | yes | The values to add up. NULLs are ignored. |
DISTINCT expression |
numeric | no | Adds up only the distinct values (rarely needed). |
How it works
SUM() adds together all non-NULL numeric values in a group. It is the aggregate you reach for to total revenue, quantities, hours, or any additive measure. Like COUNT() and AVG(), it collapses many rows into one number and is usually paired with GROUP BY.
NULLs are skipped, so a NULL never turns your total into NULL. However, if every value in the group is NULL, or there are no rows at all, SUM() returns NULL rather than 0. That distinction trips people up in reports, so wrap it in COALESCE(SUM(x), 0) when you need a zero.
As a window function with OVER(), SUM() produces running totals and cumulative sums while keeping every row.
Examples
Total of a column
SELECT SUM(total) AS revenue
FROM orders;revenue --------- 284150.75
Total per group
SELECT country, SUM(total) AS revenue
FROM orders
GROUP BY country
ORDER BY revenue DESC;country | revenue --------+---------- UAE | 141200.00 KSA | 78050.50
Conditional sum with CASE
SELECT
SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS collected,
SUM(CASE WHEN status <> 'paid' THEN total ELSE 0 END) AS outstanding
FROM orders;collected | outstanding ----------+------------ 240100.0 | 44050.7
Running total as a window function
SELECT order_date, total,
SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date;order_date | total | running_total -----------+-------+-------------- 2026-01-02| 120 | 120 2026-01-03| 80 | 200 2026-01-05| 260 | 460
Common mistakes
-- Returns NULL when there are no matching rows,
-- which then breaks downstream maths
SELECT SUM(total) FROM orders WHERE status = 'refunded';SELECT COALESCE(SUM(total), 0) FROM orders WHERE status = 'refunded';SUM() over an empty set is NULL, not 0. Use COALESCE(SUM(x), 0) whenever a missing total should read as zero.
-- SUM of an integer column can overflow the
-- integer type on large tables
SELECT SUM(quantity) FROM order_items;-- Cast to a wider type first
SELECT SUM(CAST(quantity AS BIGINT)) FROM order_items;Summing a narrow integer column across millions of rows can overflow. Cast to BIGINT or DECIMAL when totals may be large.
Performance
SUM() is cheap: it is a single pass over the rows in each group. The cost is dominated by how many rows must be read, so filtering early and indexing the columns in your WHERE and GROUP BY clauses matters more than the SUM itself.
For money, sum a DECIMAL/NUMERIC column rather than FLOAT to avoid rounding drift. See SQL data types for why float is a poor fit for currency.
Interview questions
What does SUM() return when there are no rows, or every value is NULL?
It returns NULL, not 0. Wrap it in COALESCE(SUM(x), 0) when you need a zero for empty groups.
How do you total only the rows that meet a condition?
Put a CASE expression inside SUM, for example SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END). PostgreSQL also supports SUM(total) FILTER (WHERE status = 'paid').
SELECT SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS collected
FROM orders;How do you calculate a running total in SQL?
Use SUM as a window function: SUM(total) OVER (ORDER BY order_date). This keeps every row and adds each value to the ones before it.
Does SUM() ignore NULLs?
Yes. NULL values are skipped, so a single NULL will not turn the whole total into NULL, but an all-NULL or empty group does return NULL.