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:
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:
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:
FROM products;
Given 3 items priced at ₹100, ₹200, and ₹300, manually calculating 100 + 200 + 300 = 600 verifies the aggregate result.
₹100, ₹200, ₹300
(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:
SUM With WHERE
When combined with WHERE, SQL filters rows first, then adds the values of qualifying records:
FROM sales
WHERE category = 'Electronics';
[1200, 4500, 600, 15000]
SUM and NULL (Crucial Edge Cases)
How does SUM handle missing or NULL data?
- NULL is Ignored:
SUMdoes 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, not0.
SUM() returns NULL when there are no non-NULL inputs to add. It does not automatically default to 0.➔ SUM() ➔ 600
➔ SUM() ➔ NULL
SUM vs COUNT
Beginners often confuse SUM with COUNT. Here is the distinction:
Calculates the mathematical addition of numeric values.
Example: 100 + 200 + 300 = 600
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:
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:
FROM sales;
Understanding SUM Results
The step-by-step mental evaluation model for SUM:
2. Discard any row where the expression evaluates to NULL.
3. If 0 non-NULL rows remain ➔ return NULL.
4. If non-NULL rows exist ➔ mathematically add them together ➔ return the single total.
| # | 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) |
SELECT SUM(amount) AS total_sales_revenue FROM sales;
total_sales_revenueCommon SUM Mistakes
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).
COUNT(salary) tells you how many employees earn a salary; SUM(salary) tells you the total payroll budget.
Running SUM(product_name) causes a database type error. Always verify your column contains numeric data.
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_revenuefor 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
WHEREclause 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
🎯 Knowledge Check Quiz: SQL SUM
Test your understanding of numeric summation, calculated expressions, NULL semantics, and empty result sets.