SQL Topics
SUM Function
title: SUM Function
In almost every database application, organizations need to calculate totals. Businesses constantly analyze financial transactions, sales figures, employee salaries, inventory values, and many other numerical datasets.
For example:
- What is the total revenue generated this month?
- What is the total salary expense of the company?
- How many products were sold in a particular category?
- What is the total value of inventory currently available?
- What is the total amount spent by a customer?
Manually adding thousands or millions of records would be impossible and highly inefficient.
To solve this problem, SQL provides the SUM() Function.
The SUM() function is an aggregate function that calculates the total of numeric values in a column. It is widely used in reporting systems, dashboards, financial applications, analytics platforms, and business intelligence solutions.
Understanding SUM() is essential because calculating totals is one of the most common operations performed in SQL.
What is the SUM() Function?
The SUM() function calculates the total of values stored in a numeric column.
Example:
SELECT SUM(Salary)
FROM Employees;Output:
225000Instead of displaying every salary individually, SQL returns a single total value.
Why is SUM() Important?
Consider a company with:
10,000 EmployeesManagement may need answers such as:
What is the total salary expenditure?
What is the total bonus amount?
What is the total project budget?Without SUM():
Retrieve all values
↓
Add manually
↓
Time-consumingWith SUM():
SELECT SUM(Salary)
FROM Employees;SQL calculates the result instantly.
Basic SUM() Syntax
The general syntax is:
SELECT SUM(column_name)
FROM table_name;Example:
SELECT SUM(Price)
FROM Products;SQL adds all values in the Price column and returns the total.
Understanding the Syntax
Example:
SELECT SUM(Salary)
FROM Employees;SELECT
Specifies the operation to perform.
SUM()
Calculates the total value.
Salary
Column containing numeric data.
FROM
Specifies the source table.
Employees
Table containing the data.
How SUM() Works Internally
Suppose the Salary column contains:
| Salary |
|---|
| ---------- |
| 50000 |
| 60000 |
| 45000 |
| 70000 |
Query:
SELECT SUM(Salary)
FROM Employees;Internal process:
50000
+ 60000
+ 45000
+ 70000
-----------
225000Output:
225000SQL performs the calculation automatically.
Creating a Sample Table
CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(100),
Department VARCHAR(50),
Salary DECIMAL(10,2)
);Insert sample data:
INSERT INTO Employees VALUES
(1, 'Rahul', 'IT', 50000),
(2, 'Priya', 'HR', 60000),
(3, 'Amit', 'IT', 45000),
(4, 'Neha', 'Finance', 70000);Calculating Total Salary
Query:
SELECT SUM(Salary)
FROM Employees;Result:
225000The total salary expenditure of the company is ₹225,000.
SUM() with WHERE
Very often, businesses need totals for specific records.
Example:
SELECT SUM(Salary)
FROM Employees
WHERE Department = 'IT';Process:
Filter IT Employees
↓
Add Their Salaries
↓
Return TotalOutput:
95000Only IT employee salaries are included.
SUM() with Multiple Conditions
Example:
SELECT SUM(Salary)
FROM Employees
WHERE Department = 'IT'
AND Salary > 40000;SQL:
Applies Conditions
↓
Calculates TotalOnly matching rows contribute to the sum.
SUM() with GROUP BY
This is one of the most powerful uses of SUM().
Example:
SELECT Department,
SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;Result:
| Department | TotalSalary |
|---|---|
| IT | 95000 |
| HR | 60000 |
| Finance | 70000 |
Each department receives its own total salary calculation.
Why GROUP BY + SUM() is Important
Organizations frequently need reports such as:
Sales by Region
Revenue by Product
Expenses by Department
Orders by CategorySUM() and GROUP BY together make these reports possible.
SUM() with HAVING
HAVING filters grouped results.
Example:
SELECT Department,
SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 80000;Result:
| Department | TotalSalary |
|---|---|
| IT | 95000 |
Only departments whose total salary exceeds ₹80,000 are returned.
SUM() and NULL Values
One of the most important concepts in SQL is NULL handling.
Suppose:
| Salary |
|---|
| ---------- |
| 50000 |
| NULL |
| 70000 |
Query:
SELECT SUM(Salary)
FROM Employees;Result:
120000SQL ignores NULL values.
Calculation:
50000
+ 70000
--------
120000NULL is not included.
SUM(DISTINCT)
Sometimes duplicate values exist.
Example:
| Bonus |
|---|
| -------- |
| 5000 |
| 5000 |
| 10000 |
Query:
SELECT SUM(DISTINCT Bonus)
FROM Employees;Calculation:
5000
10000
-----
15000Duplicate values are counted only once.
SUM() with Expressions
SUM() can work with calculations.
Example:
SELECT SUM(Price * Quantity)
FROM OrderDetails;Suppose:
| Price | Quantity |
|---|---|
| 100 | 5 |
| 200 | 2 |
Calculation:
(100 × 5)
+
(200 × 2)
=
900Output:
900Useful in sales and inventory systems.
Real-World Example
Consider an online store.
Orders table:
| OrderID | Amount |
|---|---|
| 1 | 500 |
| 2 | 1500 |
| 3 | 2500 |
| 4 | 1000 |
Query:
SELECT SUM(Amount)
FROM Orders;Output:
5500Management instantly knows total sales revenue.
Business Reporting Example
Monthly sales report:
SELECT
MONTH(OrderDate) AS SalesMonth,
SUM(Amount) AS TotalRevenue
FROM Orders
GROUP BY MONTH(OrderDate);Result:
| SalesMonth | TotalRevenue |
|---|---|
| January | 50000 |
| February | 72000 |
| March | 65000 |
This type of query powers business dashboards.
Performance Considerations
For small tables:
SUM()is extremely fast.
For large datasets:
Millions of Rows
Billions of Rowsperformance may depend on:
- Indexes
- Query optimization
- Database engine
- Storage architecture
Common Errors
Using SUM() on Text Columns
Wrong:
SELECT SUM(Name)
FROM Employees;SUM() only works on numeric data.
Forgetting GROUP BY
Wrong:
SELECT Department,
SUM(Salary)
FROM Employees;Most databases require:
GROUP BY Department;Misunderstanding NULL Values
Wrong assumption:
NULL = 0Reality:
NULL is ignored.Using SUM() on Incorrect Data Types
Always ensure the column contains:
INT
DECIMAL
FLOAT
NUMERICor other numeric data types.
Best Practices
Use Aliases
Good:
SELECT SUM(Salary) AS TotalSalary
FROM Employees;Improves readability.
Combine with WHERE
Filter unnecessary data before calculation.
Use GROUP BY for Reports
Generates meaningful summaries.
Understand NULL Behavior
Remember:
SUM() ignores NULL values.Optimize Large Queries
Use indexes and proper filtering.
Common Interview Questions
What does SUM() do?
SUM() calculates the total of numeric values.
Does SUM() include NULL values?
No.
NULL values are ignored.
Can SUM() be used with GROUP BY?
Yes.
This is one of its most common uses.
What is SUM(DISTINCT)?
It adds only unique values and ignores duplicates.
Summary
The SUM() function is one of the most important SQL aggregate functions. It allows developers and analysts to calculate totals quickly and efficiently, making it essential for financial reporting, business analytics, inventory management, and dashboard creation.
In this lesson, you learned:
- What SUM() is
- Why SUM() is important
- Basic syntax
- Internal working
- SUM() with WHERE
- SUM() with GROUP BY
- SUM() with HAVING
- SUM(DISTINCT)
- NULL handling
- Real-world examples
- Performance considerations
- Best practices
Mastering SUM() is essential because calculating totals is one of the most common requirements in modern database applications.
Next Step
Continue to the next lesson:
AVG() Function →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for SUM Function.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, functions, sum
Related SQL Topics