SQL Reference ยท Aggregate Functions
SQL SUM() function
Reference for the SQL SUM() aggregate: adding column values, SUM(DISTINCT), NULL and empty-set results, GROUP BY, conditional sums, overflow and rounding.
SUM() is an aggregate function that adds up the values of a numeric expression across the rows of a group. Use it for totals: revenue, quantity, hours, balances. The argument can be a column or any expression, such as quantity * unit_price.
SUM ignores NULL values. When there is nothing to add (no rows, or only NULLs), it returns NULL, not zero. Wrap it in COALESCE() when a report should show 0.
SELECT SUM(quantity * unit_price) FROM order_items;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
SUM(expression) SUM(DISTINCT expression) SUM(expression) FILTER (WHERE condition) -- standard; SQLite, PostgreSQL SUM(expression) OVER (PARTITION BY ...) -- as a window function
| Part | Description |
|---|---|
expression |
A numeric column or expression. NULL values are skipped. |
DISTINCT |
Add each different value only once. Rarely what you want for money. |
Returns: the total. Integer inputs give an integer in SQLite (and a wider integer or decimal elsewhere); real or decimal inputs give a real or decimal. NULL if no non-NULL input exists.
Dialect differences
| Behavior | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| Sum of nothing | NULL |
NULL |
NULL |
NULL |
NULL |
| Integer result type | 64-bit integer | bigint (from int) |
decimal |
Same as input (int can overflow) |
NUMBER |
| Overflow | Error ("integer overflow") | Error for bigint limit |
Rarely (uses decimal) | Error when the type is exceeded | Not applicable in practice |
| Non-overflowing, float variant | TOTAL() always returns a float and 0.0 for nothing |
Cast to numeric |
Not needed | SUM(CAST(x AS BIGINT)) |
Not needed |
FILTER (WHERE ...) |
Yes | Yes | No | No | No |
-- SQL Server: avoid int overflow by widening first SELECT SUM(CAST(quantity AS BIGINT)) FROM order_items; -- MySQL, SQL Server, Oracle: conditional sum without FILTER SELECT SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) FROM orders;
Usage notes
NULLrules.NULLinputs are skipped, soSUMof1, NULL, 2is3. If all inputs areNULL, the result isNULL. UseCOALESCE(SUM(x), 0)for a zero default, or SQLite'sTOTAL().- Money: use an integer count of cents or a
DECIMALtype. SummingREALvalues accumulates binary rounding errors, so0.1 + 0.2style results like0.30000000000000004can appear; round withROUND()when displaying. - Averages need care.
SUM(x) / COUNT(*)andAVG(x)differ whenxhasNULLs (AVGdivides by the count of non-NULLvalues). - Join fan-out. Summing a column after joining a one-to-many table repeats rows and inflates the total. Aggregate before joining, or sum from the table that holds the value.
SUMinWHEREis not allowed. Filter on a total withHAVING.- Text is not summed. SQLite converts numeric-looking text to numbers; PostgreSQL and SQL Server raise an error. Cast explicitly.
- Window form:
SUM(x) OVER (ORDER BY ...)gives running totals. See aggregates as window functions. - Booleans: to count true values, sum
CASE WHEN flag THEN 1 ELSE 0 END.
Related
COUNT(),AVG(),TOTAL(),MIN(),GROUP BY,HAVING- Lessons: Aggregate functions, Running totals