aggregate
SUM
Adds up all non-NULL numeric values in a column or expression. Returns NULL (not 0) if every input row is NULL or the result set is empty.
SUM([DISTINCT] numeric_expression)Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| DISTINCT | Sum only unique values, dropping duplicates first |
| OVER (...) | Use as a window function for running totals |
| FILTER (WHERE ...) | Postgres: sum only rows matching a predicate |
Examples
SELECT SUM(amount) FROM payments;Total payment amount across all rows
SELECT product_id, SUM(qty) FROM order_items GROUP BY product_id;Total units sold per product
SELECT COALESCE(SUM(amount), 0) FROM payments WHERE user_id = 42;Guarantees 0 instead of NULL when the user has no payments
SELECT SUM(amount) OVER (ORDER BY paid_at) AS running FROM payments;Cumulative running total ordered by date
Dialect notes / Gotcha
SUM of no rows returns NULL, not 0 — wrap in COALESCE(SUM(...), 0). Integer overflow can occur in MySQL; cast to DECIMAL or BIGINT for large aggregations.
Related functions
COUNT
Returns the number of rows matching a query, optionally counting non-NULL values of an expression or distinct values. COUNT(*) counts every row including NULLs, while COUNT(expr) skips NULLs.
AVG
Computes the arithmetic mean of non-NULL numeric values. NULL rows are excluded from both the sum and the divisor, so AVG is not equivalent to SUM/COUNT(*) when NULLs are present.
COALESCE
Returns the first non-NULL argument, or NULL if every argument is NULL. Short-circuit evaluated — later expressions are not computed once a non-NULL value is found.
MIN
Returns the smallest non-NULL value in a group, using natural ordering for the column's data type. Works for numbers, dates, and strings (lexicographic).
MAX
Returns the largest non-NULL value in a group by the column's natural ordering. Useful for latest timestamps, top prices, or alphabetically last strings.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet