Pathubs SQL Curriculum • Module 09

SQL SUM

Master the SQL mathematical aggregation function: calculate grand totals, compute calculated expressions with SUM(price * quantity), understand NULL exclusion rules, and learn why empty sets return NULL.

⏱️ Estimated Time:45 Minutes

🎯 Level:Beginner

📊 Track:Data Analytics & SQL Mastery

✨ Mode:Interactive Summation & Expression Lab

Introduction

In business intelligence, financial audits, and analytics dashboards, counting rows is only half the story. The next natural question is: "What is the total monetary or physical sum?"

What is our total revenue for Q3?
What is the total quantity of items sold across all stores?
What is the total operational expense this year?
What is the total inventory value currently stored in warehouses?

SQL answers all of these questions through the SUM aggregate function.

What Is SUM?

SUM is an aggregate function that reads numeric inputs across a set of qualifying rows and calculates their mathematical total:

SELECT SUM(amount)
FROM orders;

If your table has orders for ₹1,200, ₹4,500, and ₹600, SUM(amount) outputs 6300.

Basic SUM Syntax

The syntax of SUM takes an expression or column name inside parentheses:

SUM(expression)

The expression can be a direct numeric column (e.g. salary, price, quantity) or a mathematical calculation (e.g. price * quantity).

Your First SUM Query

Let's look at a simple product price query:

SELECT SUM(price)
FROM products;

Given 3 items priced at ₹100, ₹200, and ₹300, manually calculating 100 + 200 + 300 = 600 verifies the aggregate result.

Diagram 1: Basic SUM Aggregation

Numeric Column Values:
₹100, ₹200, ₹300

➔ SUM(price) ➔

Result: ₹600
(100 + 200 + 300)

SUM With a Column

SUM operates strictly on numeric data types (such as INTEGER, DECIMAL, NUMERIC, and FLOAT). Passing text or timestamps causes a type error:

SELECT SUM(amount) FROM sales;

SUM With WHERE

When combined with WHERE, SQL filters rows first, then adds the values of qualifying records:

SELECT SUM(amount)
FROM sales
WHERE category = 'Electronics';

Diagram 2: WHERE Filter + SUM Pipeline

All 8 Sales Rows

➔ WHERE category = 'Electronics' ➔

4 Matching Electronics Rows
[1200, 4500, 600, 15000]

➔ SUM(amount) ➔

₹21,300 Total

SUM and NULL (Crucial Edge Cases)

How does SUM handle missing or NULL data?

NULL is Ignored: SUM does not treat NULL as zero; it simply skips the NULL value and adds the remaining numeric numbers. For example: 100 + NULL + 200 + NULL + 300 = 600.
Empty Input Returns NULL: If a table has 0 matching rows, or if all inputs are NULL, standard SQL returns NULL, not 0.

⚠️

Verified SQL Standard Behavior: PostgreSQL, MySQL, and standard SQL explicitly document that SUM() returns NULL when there are no non-NULL inputs to add. It does not automatically default to 0.

Diagram 3: NULL Ignored vs Empty Set NULL

Values: 100 + NULL + 200 + NULL + 300
➔ SUM() ➔ 600

Values: [All NULL or 0 Rows]
➔ SUM() ➔ NULL

SUM vs COUNT

Beginners often confuse SUM with COUNT. Here is the distinction:

SUM(amount)

Calculates the mathematical addition of numeric values.
Example: 100 + 200 + 300 = 600

COUNT(amount)

Calculates the quantity of non-NULL items.
Example: [100, 200, 300] = 3 items

SUM With Calculated Expressions

You can perform mathematical operations inside the SUM parentheses before adding the rows together:

SELECT SUM(price * quantity) AS total_order_value
FROM order_items;

For each row, SQL computes price * quantity, and then sums those computed amounts into a grand total.

Giving SUM an Alias

Always use the AS keyword to give the calculated total a clear name:

SELECT SUM(amount) AS total_sales
FROM sales;

Understanding SUM Results

The step-by-step mental evaluation model for SUM:

Scan qualifying candidate rows from source table.
Discard any row where the expression evaluates to NULL.
If 0 non-NULL rows remain ➔ return NULL.
If non-NULL rows exist ➔ mathematically add them together ➔ return the single total.

⚡ Live Interactive SUM Lab

📦 Sales Records & Contribution8 Sample Orders

#	Product	Category	Qty	Amount	SUM Contribution
1	Wireless Mouse	Electronics	2	₹1,200	✓ ₹1,200 is numeric (Added to total)
2	Desk Lamp	Home Office	1	NULL	✗ Amount is NULL (Ignored by SUM)
3	Mechanical Keyboard	Electronics	1	₹4,500	✓ ₹4,500 is numeric (Added to total)
4	Gel Pen Pack	Stationery	5	₹250	✓ ₹250 is numeric (Added to total)
5	USB-C Cable	Electronics	3	₹600	✓ ₹600 is numeric (Added to total)
6	Ergonomic Chair	Home Office	1	₹8,500	✓ ₹8,500 is numeric (Added to total)
7	Sticky Notes	Stationery	4	NULL	✗ Amount is NULL (Ignored by SUM)
8	Gaming Monitor	Electronics	1	₹15,000	✓ ₹15,000 is numeric (Added to total)

✍️ SQL Aggregate Query● Scalar Output

SELECT SUM(amount) AS total_sales_revenue
FROM sales;

₹30,050

Output column: total_sales_revenue

Ticker: ₹1200 + ₹4500 + ₹250 + ₹600 + ₹8500 + ₹15000 = ₹30,050

Common SUM Mistakes

1. Assuming SUM on 0 Rows Returns 0

When no rows match your filter, SUM() returns NULL, not 0. If your application requires a fallback 0, you will later learn functions like COALESCE(SUM(amount), 0).

2. Confusing SUM With COUNT

COUNT(salary) tells you how many employees earn a salary; SUM(salary) tells you the total payroll budget.

3. Attempting to SUM Non-Numeric Fields

Running SUM(product_name) causes a database type error. Always verify your column contains numeric data.

🧠 Interactive Challenge: Predict The Result (1 of 3)

SELECT SUM(amount) FROM sales;

With amounts [1200, NULL, 4500, 250, 600, 8500, NULL, 15000], what is the result of SUM(amount)?

Practical Step-by-Step Exercises

Task Goal	Target Table	Required SQL Solution	Pattern Used
1. Total Sales Amount	`sales`	`SELECT SUM(amount) FROM sales;`	Basic column SUM
2. Total Quantity Sold	`order_items`	`SELECT SUM(quantity) FROM order_items;`	Volume summation
3. Total Electronics Sales	`sales`	`SELECT SUM(amount) FROM sales WHERE category = 'Electronics';`	WHERE + SUM
4. Total Line-Item Value	`order_items`	`SELECT SUM(price * quantity) AS total_val FROM order_items;`	Expression aggregation
5. Total Expenses in 2026	`expenses`	`SELECT SUM(cost) FROM expenses WHERE expense_year = 2026;`	Filtered year sum

SUM Best Practices

Always Alias Aggregate Results: Write SUM(revenue) AS total_revenue for clean analytical reporting.
Handle NULL Semantics Carefully: Remember that NULL rows do not contribute to the total sum.
Combine With Expressions: Calculate unit totals directly inside SUM(qty * unit_price) rather than running multiple separate steps.
Filter Before Aggregating: Place precise filters in your WHERE clause to isolate specific categories, dates, or regions.

What You Should Know Now

✓SUM(col): Mathematically totals numeric values
✓SUM(expr): Totals calculated expressions (e.g. price * qty)
✓NULL Behavior: Silently skipped during addition
✓Empty Sets: Returns NULL when 0 non-NULL rows exist
✓SUM vs COUNT: SUM totals values; COUNT counts items
✓Aliases: Formats clean, descriptive output headers

TEST YOUR KNOWLEDGE

Knowledge Assessment: SQL SUM Function

Test your understanding of numeric accumulation, total computations, NULL arithmetic, and monetary aggregations.

Question 1 of 8Current Score: 0 / 0

What is the primary purpose of the SQL SUM aggregate function?