SUM() Function
Learn how to calculate the total of numeric values using the SUM() aggregate function in SQL Server.
What is SUM()?
The SUM() function is an aggregate function in SQL Server used to calculate the total of numeric values from multiple rows.
It is commonly used for calculating total salary, total sales, total marks, total quantity, total price and other numerical values.
SUM() adds the numeric values together and returns their total.
SUM() Syntax
SELECT SUM(column_name)
FROM table_name;
We provide the numeric column inside the SUM() function.
Example Table
Consider the following Employees table:
| EmployeeId | EmployeeName | Department | Salary |
|---|---|---|---|
| 1 | Rahul | IT | 50000 |
| 2 | Priya | HR | 40000 |
| 3 | Amit | IT | 60000 |
| 4 | Neha | HR | 45000 |
| 5 | Rohit | Sales | 35000 |
Basic SUM() Example
To calculate the total salary of all employees:
SELECT SUM(Salary) AS TotalSalary
FROM Employees;
Calculation
50000 + 40000 + 60000 + 45000 + 35000
= 230000
Output
| TotalSalary |
|---|
| 230000 |
SUM() with WHERE
SUM() can be combined with WHERE when we want to calculate the total only for records satisfying a particular condition.
For example, calculate the total salary of employees whose salary is greater than 40000:
SELECT SUM(Salary) AS TotalSalary
FROM Employees
WHERE Salary > 40000;
Calculation
50000 + 60000 + 45000
= 155000
The result is 155000.
SUM() with GROUP BY
SUM() is commonly used with GROUP BY to calculate totals for each group.
For example, calculate the total salary for each department:
SELECT Department,
SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;
Output
| Department | TotalSalary |
|---|---|
| HR | 85000 |
| IT | 110000 |
| Sales | 35000 |
SUM() with HAVING
HAVING can be used to filter groups based on their calculated SUM() value.
For example, display only departments whose total salary is greater than 80000:
SELECT Department,
SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 80000;
Output
| Department | TotalSalary |
|---|---|
| HR | 85000 |
| IT | 110000 |
SUM() and NULL Values
SUM() ignores NULL values when calculating the total.
For example, if a Salary column contains:
50000
60000
NULL
40000
SUM() calculates the total using the available numeric values:
50000 + 60000 + 40000
= 150000
SUM(DISTINCT)
SUM(DISTINCT column) calculates the total using only unique values.
SELECT SUM(DISTINCT Salary) AS TotalSalary
FROM Employees;
DISTINCT removes duplicate values before calculating the total.
SUM() in Reports
SUM() is frequently used in reports to calculate business totals such as:
- Total sales
- Total salary
- Total product quantity
- Total order amount
- Total revenue
Important Points
- SUM() works with numeric values.
- SUM() calculates the total of values.
- SUM() ignores NULL values.
- SUM() can be combined with WHERE.
- SUM() can be combined with GROUP BY.
- HAVING can filter grouped SUM() results.
- SUM(DISTINCT column) calculates the total of unique values.
Summary
SUM() is an aggregate function used to calculate the total of numeric values. It is commonly used with WHERE, GROUP BY and HAVING clauses to perform detailed calculations and generate meaningful summary reports.