Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT SUM(quantity * unit_price) FROM order_items;

Try it

SQL Totals, per-group totals and an expression ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Empty sums, NULL and conditional sums ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
-- 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

  • NULL rules. NULL inputs are skipped, so SUM of 1, NULL, 2 is 3. If all inputs are NULL, the result is NULL. Use COALESCE(SUM(x), 0) for a zero default, or SQLite's TOTAL().
  • Money: use an integer count of cents or a DECIMAL type. Summing REAL values accumulates binary rounding errors, so 0.1 + 0.2 style results like 0.30000000000000004 can appear; round with ROUND() when displaying.
  • Averages need care. SUM(x) / COUNT(*) and AVG(x) differ when x has NULLs (AVG divides by the count of non-NULL values).
  • 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.
  • SUM in WHERE is not allowed. Filter on a total with HAVING.
  • 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.