SUM Function

Add up numeric values — with support for conditional totals

Intermediate 10 minSUMaggregate

Summing Values

SUM adds all non-NULL values in a numeric column. NULLs are skipped entirely — they are not treated as zero, they're simply ignored as if they don't exist. Here's what to know:

Numeric columns only — Attempting to SUM a text column returns 0 or an error depending on the database. Always verify your column type before using SUM to avoid silent incorrect results.

Empty set returns NULL, not 0 — SUM of zero matching rows returns NULL. This catches many people off guard. Use COALESCE(SUM(col), 0) to safely default to zero when no rows match your filter conditions.

Conditional totals with CASE — SUM(CASE WHEN status = 'delivered' THEN total ELSE 0 END) adds only delivered order amounts. This powerful pattern creates pivot-style calculations in a single query without restructuring your source data.

Knowledge Canvas

How SUM Works

Add up numeric values

SUM adds all non-NULL values in a column
NULL values are silently skipped
SUM of all NULLs = NULL (not 0)
SUM(DISTINCT col) adds only unique values

NULL Behavior

SUM + NULL interactions

SUM skips NULLs: SUM(10, NULL, 20) = 30
All NULLs → SUM returns NULL, not 0
Use COALESCE: COALESCE(SUM(col), 0)
SUM with LEFT JOIN: unmatched rows contribute NULL → safe

SUM Gotchas

Precision and type issues

Floating point: SUM may have rounding artifacts
Use ROUND(SUM(...), 2) for currency
SUM on text columns → error or unexpected behavior
Double-counting in joins: SUM inflates with duplicate rows

SUM Patterns

Common real-world uses

Revenue: SUM(quantity * price)
Conditional: SUM(CASE WHEN paid THEN amount ELSE 0 END)
Running total: SUM(amount) OVER (ORDER BY date)
Percentage: SUM(col) * 100.0 / SUM(SUM(col)) OVER ()

Syntax

Syntax Template

1-- Plain sum (NULLs are ignored)

2SELECT

3 SUM(column)

4FROM table;

6-- Conditional sum with CASE

7SELECT

8 SUM(

9 CASE

10 WHEN cond THEN col

11 ELSE 0

12 END

13 )

14FROM table;

SUM(column)Adds all non-NULL values in the column

SUM + CASEConditional summing — sum only matching rows

Sample Data

orders

12 rows

id	status	total
5001	delivered	59.98
5002	delivered	449
5003	shipped	12.5
5004	delivered	89.99
5005	pending	245
5006	delivered	159
5007	shipped	45.5
5008	cancelled	79
5009	delivered	425
5010	pending	18.99
5011	delivered	134
5012	cancelled	65.5

products

10 rows

id	name	category	price	stock
P101	Wireless Mouse	Electronics	24.99	150
P102	Office Desk	Furniture	189	25
P103	LED Desk Lamp	Furniture	45.5	80
P104	USB-C Cable	Electronics	12.99	500
P105	Bluetooth Speaker	Electronics	79	60
P106	Office Chair	Furniture	95	15
P107	Coffee Maker	Appliances	89.99	40
P108	Notebook Set	Stationery	8.5	200
P109	Wireless Headphones	Electronics	159	30
P110	Standing Desk	Furniture	425	8

Worked Example

Calculate total revenue from all orders, and separately the revenue from delivered orders only.

SQL

1SELECT

2 SUM(total) AS all_revenue,

3 SUM(

4 CASE

5 WHEN status = 'delivered' THEN total

6 ELSE 0

7 END ) AS delivered_revenue

8FROM orders;

SUM(total) adds every order's total regardless of status. The CASE expression inside the second SUM only contributes the total when the order is delivered — cancelled and shipped orders contribute 0.

Output

1 row

all_revenue	delivered_revenue
1783.46	1316.97

Key Concepts

1SUM of an empty set returns NULL, not 0

2Use COALESCE(SUM(col), 0) to safely default to zero

3SUM + CASE WHEN creates conditional totals in one query

4SUM ignores NULLs — they don't contribute 0, they're skipped

Pro Tip

Revenue, total quantity, cumulative costs — most business metrics are sums. Understanding SUM's NULL handling is essential for accurate reporting.

When to Use

Total revenue, sum of quantities ordered, inventory value, monthly sales figures.

Challenge

Solve the problem below

Calculate the total value of all products in stock (price × stock for each product, then sum everything). Name it total_inventory_value.

Your Query

COUNT Function

AVG Function