SUM

SUM: Totaling a Numeric Column

SUM(column) adds up all the (non-NULL) numeric values in a column across the rows in scope.

sql
SELECT SUM(amount) FROM orders;

Using the running example (amounts: 250.00, NULL, 400.00, 150.00, 300.00):

SUM(amount)
------------
    1100.00

SUM adds 250 + 400 + 150 + 300 = 1100. Bob's NULL amount is skipped, not treated as 0.

SUM Ignores NULLs — It Does Not Zero Them

This is worth repeating because it's a common source of bugs: if you expected NULL to act like 0 in a total, you'd get 1100 either way in this example (since adding 0 changes nothing) — but the distinction becomes visible when you divide, as in AVG, or when every value is NULL:

sql
SELECT SUM(amount) FROM orders WHERE customer_name = 'NoOneReal';
-- NULL   (no matching rows at all → NULL, not 0)

If your application logic expects a number and might get NULL back (e.g. "total sales today" when there were no sales), wrap it:

sql
SELECT COALESCE(SUM(amount), 0) AS total_amount FROM orders WHERE order_date = CURRENT_DATE;

SUM with a Filter

sql
SELECT SUM(amount) FROM orders WHERE status = 'completed';
-- 250.00 + 400.00 + 300.00 = 950.00

SUM of an Expression

SUM can take any numeric expression, not just a bare column:

sql
SELECT SUM(amount * 0.18) AS total_tax FROM orders;
-- SUM of 18% tax on each non-NULL amount

Data Type of the Result

The result type of SUM generally widens to avoid overflow: summing an INT column typically yields a BIGINT-class result in many engines, and summing a DECIMAL(10,2) column stays decimal. This matters for very large tables where a plain INT sum could overflow — the engine handles this automatically in most dialects, but it's worth being aware of when working with huge counters.

Edge Cases

• SUM() over zero rows (or all-NULL values) returns NULL, not 0.
• SUM(DISTINCT column) is legal — it sums only the distinct values first (rarely useful, but valid: SUM(DISTINCT amount) on 250, 400, 150, 300 — no duplicates here, so it'd still be 1100, but if Alice had two 250.00 orders, SUM(DISTINCT amount) would only add 250 once).
• Summing mixed positive/negative values (e.g. a transactions table with debits as negative) works exactly like ordinary arithmetic addition.

Key Takeaways / Interview Q&A

Q: Does SUM() treat NULL as zero? A: No — SUM ignores NULL values entirely; they don't reduce or contribute to the total.

Q: What does SUM() return over an empty result set? A: NULL, not 0. Use COALESCE(SUM(x), 0) if you need a guaranteed numeric zero.

Q: Can you SUM an expression instead of a raw column? A: Yes — SUM(amount * 0.18) or any numeric expression works.

Q: What does SUM(DISTINCT column) do? A: Removes duplicate values first, then sums what remains — different from a plain SUM whenever the column has repeated values.