SQL Sum

The SQL SUM() function adds up every value in a numeric column and returns a single total. It is one of the five core aggregate functions in SQL, alongside COUNT(), AVG(), MIN(), and MAX(), and it is the tool you reach for any time you need a total: total revenue, total quantity shipped, total hours worked. Used on its own, SUM() collapses an entire table into one number. Combined with GROUP BY, it produces one total per group, which is how most real-world reporting queries — ‘revenue by region,’ ‘hours by employee’ — are actually built.

Overview: How SUM Works

SUM() is an aggregate function: it operates on a set of rows and collapses them into a single value, rather than returning one output per input row like a scalar function does. When the database engine executes a query containing SUM(column), it first determines the set of rows that qualify (after any WHERE filtering), then walks through that set adding up the values found in column, keeping a running numeric accumulator. When there is no GROUP BY clause, the entire qualifying row set is treated as a single group, so the query returns exactly one row with one total.

When a GROUP BY clause is present, the engine’s job changes: instead of one running total, it must partition the qualifying rows into buckets that share the same value(s) in the grouping column(s), and compute a separate running total for each bucket. Internally this is usually done by sorting the rows on the grouping key (so identical keys become adjacent) or by building an in-memory hash table keyed on the grouping value — the query planner picks whichever strategy is cheaper, often using an index on the grouping column to avoid a full sort. The result is one output row per distinct group, each carrying its own SUM().

A detail that trips up almost everyone at some point: SUM() silently ignores NULL values. A NULL in the summed column is simply skipped, as if that row were not there for the purposes of the calculation — it is not treated as zero. This matters because it means SUM(quantity) and COUNT(quantity) can disagree with COUNT(*) when some rows have missing data, and it means that if every row in a group has NULL in that column (or the group is empty entirely, e.g. no rows matched the WHERE clause), SUM() returns NULL, not 0. This is one of the most common sources of unexpected blank cells in reports, and it’s covered with a worked example below.

One more portability note: SUM() expects a numeric column. Standard SQL engines like PostgreSQL and SQL Server will raise a type error if you try to sum a genuinely non-numeric text column. SQLite, because of its dynamic typing system, is more forgiving and will treat non-numeric text as 0 rather than erroring — convenient for quick scripts, but a trap if you rely on it in production logic. Always make sure the column you are summing is actually numeric.

Syntax

SELECT SUM(column_name) AS alias
FROM table_name
WHERE condition
GROUP BY grouping_column
HAVING SUM(column_name) condition
ORDER BY alias;
Part Meaning
SUM(column_name) Adds every non-NULL value in column_name across the qualifying rows (or per group).
SUM(DISTINCT column_name) Optional: sums only the distinct values in the column, so duplicate values are counted once.
AS alias Gives the resulting total a readable column name in the output.
WHERE condition Filters individual rows before any summing happens. Cannot reference SUM() directly.
GROUP BY grouping_column Optional: produces one total per distinct value of the grouping column(s) instead of one grand total.
HAVING condition Filters groups after summing — this is where you compare against a SUM() result.
ORDER BY Optional: sorts the final result rows, commonly by the computed total.

Examples

All three examples below use the same small self-contained orders table.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer TEXT,
  product TEXT,
  category TEXT,
  amount REAL,
  order_date TEXT
);
INSERT INTO orders (customer, product, category, amount, order_date) VALUES
('Alice', 'Widget', 'Hardware', 250.00, '2024-01-05'),
('Bob', 'Gadget', 'Electronics', 400.00, '2024-01-10'),
('Alice', 'Gizmo', 'Electronics', 150.00, '2024-02-01'),
('Carol', 'Widget', 'Hardware', 300.00, '2024-02-15'),
('Bob', 'Widget', 'Hardware', 100.00, '2024-03-01'),
('Carol', 'Gadget', 'Electronics', 500.00, '2024-03-10');

Example 1: A single grand total

SELECT SUM(amount) AS total_revenue FROM orders;

Result:

total_revenue
1700.0

With no WHERE and no GROUP BY, all six rows form a single group, so the engine adds every value in amount (250 + 400 + 150 + 300 + 100 + 500) into one number.

Example 2: One total per group

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer
ORDER BY total_spent DESC;

Result:

customer total_spent
Carol 800.0
Bob 500.0
Alice 400.0

Here GROUP BY customer partitions the six rows into three buckets (Alice, Bob, Carol), and SUM(amount) is computed separately within each bucket before ORDER BY sorts the three resulting rows from highest to lowest spender.

Example 3: Filtering on the aggregate with HAVING

SELECT category, SUM(amount) AS category_total
FROM orders
GROUP BY category
HAVING SUM(amount) > 700;

Result:

category category_total
Electronics 1050.0

Hardware totals 250 + 300 + 100 = 650, and Electronics totals 400 + 150 + 500 = 1050. Both totals are computed first, and only afterward does HAVING SUM(amount) > 700 discard the Hardware group, leaving only Electronics in the final output.

Using DISTINCT with SUM

CREATE TABLE payments (
  id INTEGER PRIMARY KEY,
  amount REAL
);
INSERT INTO payments (amount) VALUES (100), (100), (250), (250), (400);

SELECT SUM(amount) AS sum_all, SUM(DISTINCT amount) AS sum_distinct FROM payments;

Result:

sum_all sum_distinct
1100.0 750.0

sum_all adds every row’s value (100+100+250+250+400 = 1100), while SUM(DISTINCT amount) first collapses the values to their unique set (100, 250, 400) and then adds those (= 750). DISTINCT is rarely what you want for a revenue-style total — use it only when duplicate values in the column genuinely represent the same fact counted twice.

Under the Hood: Query Processing Order

SQL is written in the order SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY, but the engine does not execute it in that order. The actual logical processing order is:

  1. FROM — identify the source table(s).
  2. WHERE — filter individual rows before any aggregation. At this point aggregate functions like SUM() do not exist yet, which is why WHERE can never reference them.
  3. GROUP BY — partition the surviving rows into groups.
  4. HAVING — now that per-group aggregates like SUM() have been computed, filter out whole groups based on them.
  5. SELECT — compute the final output columns and aliases.
  6. ORDER BY — sort the resulting rows, optionally by an aggregate or its alias.

Walking through Example 3: the engine reads all six rows from orders (FROM), applies no filter since there is no WHERE, groups the rows by category into Hardware and Electronics (GROUP BY), computes SUM(amount) for each group (650 and 1050), then evaluates HAVING SUM(amount) > 700 and drops Hardware (HAVING), and only then produces the final two-column output (SELECT). This ordering is exactly why aggregate conditions belong in HAVING, never in WHERE — the database simply has not computed the aggregate yet by the time WHERE runs.

Common Mistakes

Mistake 1: Putting an aggregate condition in WHERE

SELECT customer, SUM(amount)
FROM orders
WHERE SUM(amount) > 400
GROUP BY customer;

This fails with an error such as ‘misuse of aggregate: SUM()’. As explained above, WHERE is evaluated before grouping and aggregation happen, so SUM() simply does not exist yet at that stage of processing — the engine has no per-group total to compare against. The fix is to move the condition into HAVING, which runs after the totals are computed:

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer
HAVING SUM(amount) > 400
ORDER BY customer;

Result:

customer total_spent
Bob 500.0
Carol 800.0

Alice’s total of exactly 400 is excluded because the condition is a strict greater-than.

Mistake 2: Assuming SUM returns 0 instead of NULL

CREATE TABLE inventory (
  id INTEGER PRIMARY KEY,
  item TEXT,
  quantity INTEGER
);
INSERT INTO inventory (item, quantity) VALUES
('Bolts', 100),
('Screws', NULL),
('Nails', 50);

SELECT SUM(quantity) AS total_quantity FROM inventory;
SELECT SUM(quantity) AS total_quantity FROM inventory WHERE item = 'Rivets';

Result of the first query:

total_quantity
150

Result of the second query:

total_quantity
NULL

The first query correctly ignores the NULL row for ‘Screws’ and sums only 100 + 50 = 150. The second query filters to a customer that does not exist at all — no rows survive WHERE, so there is nothing to sum, and SUM() returns NULL rather than 0. Code that expects a number and then tries to do arithmetic on that NULL will itself become NULL, which silently breaks downstream calculations. Guard against this with COALESCE() whenever a numeric default matters:

SELECT COALESCE(SUM(quantity), 0) AS total_quantity
FROM inventory
WHERE item = 'Rivets';

Result:

total_quantity
0

Best Practices

  • Filter rows with WHERE before aggregation, and filter groups with HAVING after aggregation — never try to reference SUM() inside WHERE.
  • Wrap SUM() in COALESCE(SUM(column), 0) whenever the result feeds into further math or is displayed to a user who expects a number, not a blank.
  • Remember that SUM() ignores NULL values but not 0 values — a row with 0 still counts as a real contribution, it just adds nothing.
  • Add an explicit ORDER BY whenever the order of grouped results matters; grouped output order is not guaranteed unless you sort it yourself.
  • Be careful summing a column that has been duplicated by a join (a classic ‘fan-out’ bug) — join a one-to-many relationship carelessly and SUM() will double- or triple-count values. Aggregate before joining, or use a subquery, when this risk exists.
  • Only use SUM(DISTINCT column) when duplicate values genuinely represent the same underlying fact; otherwise it silently discards legitimate repeated amounts.
  • On large tables, an index on the columns used in WHERE and GROUP BY helps the engine filter and partition rows efficiently before it has to scan for the sum.
  • Confirm the column you are summing is genuinely numeric — some databases error on non-numeric input, while others (like SQLite) coerce it silently, which can mask a data-quality problem.

Practice Exercises

  • Exercise 1: Using the orders table from the examples, write a query that returns the total amount for each product, sorted from highest total to lowest.
  • Exercise 2: Write a query against orders that returns only the customers whose combined amount across all their orders is less than 500, using HAVING.
  • Exercise 3: Using the inventory table from the Common Mistakes section, write a query that returns the total quantity across all items, but returns 0 instead of NULL if every row happened to have a missing quantity. (Hint: combine SUM() with COALESCE().)

Summary

  • SUM(column) adds up all non-NULL numeric values in a column, returning one grand total with no GROUP BY, or one total per group when GROUP BY is used.
  • NULL values are skipped by SUM(); if a group has no matching rows or only NULLs, the result is NULL, not 0 — use COALESCE(SUM(column), 0) to guard against this.
  • Filter individual rows before aggregation with WHERE; filter groups after aggregation with HAVING — aggregate functions cannot appear in WHERE.
  • The logical processing order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY, which explains why HAVING can reference SUM() but WHERE cannot.
  • SUM(DISTINCT column) sums only unique values — use it deliberately, not by default.
  • Watch for join fan-out inflating sums, and always sort grouped results explicitly with ORDER BY when order matters.