The T-SQL SUM function is one of the fundamental functions. Its purpose is to calculate the sum of all values in the specified column – note that it applies to numeric columns only.
Below is the syntax of SQL SUM.
-- Aggregate Function Syntax
SUM ( [ ALL | DISTINCT ] expression )
-- Analytic Function Syntax
SUM ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause)
- ALL is a default value, not mandatory to pass. It allows for returning the sum of all values.
- DISTINCT specifies that it has to return the sum of unique values only, ignoring duplicate values.
- Expression is the exact or approximate numeric data type category, except for the bit data type.
- OVER clause with partition_by_clause and order_by_clause is for using this function on a specific partition.
- partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied
- order_by_clause determines the logical order in which the operation is performed.
To demonstrate the usage of the SQL SUM function, I have created a table named Employee. It has 5 columns – name, salary, rent, tax, and employment nature. The following use cases will be in the focus:
- Basic or Simple use of SUM function
- Using SUM with CASE expressions
- Using SUM with GROUP BY statement
- Using SUM with HAVING clause
- Applying SUM for multiple or complex arithmetic expressions
Also, we’ll learn other use cases like using SUM with WHERE clause, ORDER BY clause, or using DISTINCT in the SUM function to get the sum of unique values. But first things first.
Use Case 1: Simple SQL SUM Function
As stated above, I have a table named Employee with few columns. Assume that your company wants to know how much salary cost they are spending on all employees. To get the value, you just need to add the salary of all employees. SUM () will make your life easy, providing such reports quickly.
We will use the SUM () function on the salary column, and the output will be the total salary cost spent on all the company’s employees:
USE DBName
GO
SELECT SUM(Salary) As [Total Employees Cost]
FROM [dbo].[Employee]
If you have any NULL value in the column, this function will ignore it and proceed with the next row value. Thus, you may have NULL for any employee who left the organization – the company is not paying anything to that resource.
You can see the output below – it shows the amount of $885000 spent on employees’ salaries:
If there are duplicate values on any column, and you want to get the sum of all unique values, you can use the DISTINCT argument:
USE DBName
GO
--Get the sum of all values from column salary
SELECT SUM(Salary) As [Total Employees Cost]
FROM [dbo].[Employee]
GO
--Get the sum of all unique values from column salary
SELECT SUM(DISTINCT Salary) As [Total Employees Cost]
FROM [dbo].[Employee]
GO
I have executed both statements together. First, it was the one from the previous step with all salary values. The second was for unique values – it used the DISTINCT argument.
Have a look at the below screenshot – it demonstrates the outputs of both statements, and they are different. When I use the SUM function for all values by default, the output is $885000. When I am using the DISTINCT argument to get the sum of unique values only, the output is $65000.
Thus, we have similar values in this column. in business terms, it means we have few employees receiving the same amounts as their salary.
This example demonstrates the use of the DISTINCT argument to get the sum of all unique values. In practice, it lets us avoid duplicate values from the final result.
We can also use the WHERE clause in SQL SUM (). Let’s say your company wants to get the total cost it spends for employees who are getting more than $100000 as salary, and not a permanent employee to understand the cost distribution. The WHERE clause will filter the result and provides the total cost as per business requirements.
In the below T-SQL statement, you can see the WHERE clause added on the Employment Nature column. This column keeps a record of each employee, whether they are permanent or contractual employees.
Note that I have used the AND operator to club both requirements:
USE DBName
GO
SELECT SUM(Salary) As [Total Employees Cost]
FROM [dbo].[Employee]
WHERE [Employment Nature] = ‘Contractor’ AND Salary >100000
GO
The output shows that the company spends $240000 on those employees receiving $100000 or more, and not permanent employees.
Use Case 2: SQL SUM Function with CASE Expressions
In our scenario, we want to know how many employees are working as permanent along with their other details. to get this result, we can use the CASE expression in the SUM () function. Although, you can easily get this result by simply putting WHERE clause, sometimes you need CASE and SUM in a complex query requirement.
Have a look at below T-SQL statement with the CASE expression inside the SUM () function:
--USE SUM function with CASE expression
SELECT
SUM(
CASE
WHEN [Employment Nature] = ‘Permanent’ THEN 1
ELSE 0
END) As [Total Employees],
FROM [dbo].[Employee]
The output of the above statements shows 4 – the company has only 4 permanent employees as per their records in the Employee table.
Let’s take another example of CASE with SUM ().
While exploring use case 1, we found the total costs spent on contractual employees who receive $100000 or more in the above section under use case 1. Now, we want to find the number of such employees. And, the CASE expression will come in to picture again.
We can use CASE in the same T-SQL statement used before. Below is the example:
USE DBName
GO
SELECT SUM(Salary) As [Total Employees Cost],
SUM(CASE WHEN [Employment Nature] = ‘Contractor’ AND Salary >100000 THEN 1
ELSE 0
END) AS [Contractors Headcount]
FROM [dbo].[Employee]
WHERE [Employment Nature] = ‘Contractor’ AND Salary >100000
GO
The output is showing us 2 contractors working and getting more than $100000.
You can use various aspects of the CASE expression in the SELECT statement along with the SUM () function to fulfill your business needs.
Use Case 3: SQL SUM Function with GROUP BY
Let’s assume that our company wants to know how much money in total it spends on its permanent employees, and how much money it spends on contractual employees. We can get this result set using the SUM function along with the GROUP BY statement.
Look at the below T-SQL statement. I have used the GROUP BY statement on the Employment Nature column and applied the SUM function on the Salary column to get the total salary of each type of employee categorized under their employment nature.
USE DBName
GO
SELECT [Employment Nature], SUM(Salary) As [Total Employees Cost]
FROM [dbo].[Employee]
GROUP BY [Employment Nature]
The result set provides the total money spent on contractors and permanent employees. If you add both those numbers, their sum will be the total money spent on their employees’ salary (compare this number with the first screenshot of this article).
USE Case 4: SQL SUM Function with HAVING Statement
Now, we’ll explore the usage of the SUM function with HAVING and GROUP BY statements together. Our example will display the list of employees who receive more than $150000 per year. We can get it by simply using the SELECT statement with the WHERE clause, but I will show you how to get the same result with HAVING, GROUP BY, and SUM.
There are 2 T-SQL scripts. The output of both statements is the same, but the first script uses a simple SELECT statement, whereas the second code uses the SUM function along with GROUP BY and HAVING clauses.
USE DBName
GO
SELECT name, salary
FROM [dbo].[Employee]
WHERE Salary >=150000
GO
--SUM with HAVING
SELECT name, SUM(salary) AS [Salary]
FROM [dbo].[Employee]
GROUP BY name HAVING SUM(salary) >=150000
GO
See the outputs of both scripts:
There is another aspect of using these statements. I have passed arithmetic expressions in the SUM function to get the rent and tax expenses of employees. Then, I applied GROUP BY and HAVING statements on the Salary column. In simple words, I wanted to know the total rent and tax expenses spent by employees with a salary of $150000 or more.
I also used two queries. The first lists the details of all employees who receive $150000 or more. This output is fetched just for validation and verification. The second one uses the SUM function on two columns (rent and tax) as their total expenses. Then is applies GROUP BY and HAVING clauses:
USE DBName
GO
SELECT name, salary
FROM [dbo].[Employee]
WHERE Salary >=150000
GO
--SUM with HAVING and GROUP BY
SELECT name, SUM( rent + tax) AS [Total Expenses]
FROM [dbo].[Employee]
GROUP BY name HAVING SUM(salary) >=150000
GO
Let’s analyze the output. The first query displays all details of employees with salaries of $150000 or more. Now, look at the second query result. Here, we have fetched the employees and their expenses based on their salary cutoff which is $150000 or more. This way, we have verified that our query with the SUM, GROUP BY, and HAVING clauses returns correct results.
Use Case 5: SQL SUM Function with Multiple Arithmetic Expressions
The SUM function offers great flexibility when it comes to arithmetic expressions. We can use multiple arithmetic expressions, such as adding or subtracting between multiple column values when needed. Let’s refer to an example.
We want to pull a report about the total savings of our employees. There can be various columns of expenses or allowances, such as house rent allowance, travel allowances, meal allowance, etc. Our table Employee has two columns related to the employees’ expenses – the rent paid for accommodation and the taxes paid to the government. You can understand the concept and apply it to more columns in a similar manner.
USE DBName
GO
--List table data for reference
SELECT *
FROM [dbo].[Employee]
GO
--SUM with multiple arithmetic expressions
SELECT name, SUM(salary – (rent + tax)) AS [Total Savings]
FROM [dbo].[Employee]
GROUP BY name
GO
The first query displays all data from the table Employee. We will use this data as a reference to verify our actual output returned by the second query about the total savings for each employee.
We have got this by adding all expenses and then subtracting all expenses from the employee’s salary as given in the SUM function statement. You can verify it by manually calculating this data from the first result set for any employee to get it validated.
Conclusion
We have explored multiple use cases of SQL SUM function with other clauses. By the way, modern digital tools for SQL Server specialists can simplify these tasks drastically. For instance, dbForge SQL Complete includes a feature that can calculate aggregate functions from the ready result set in the SSMS result grid.
You might share your favorite tips related to the SQL SUM function. You are welcome to use the Comments section.
Tags: sql functions, t-sql statements Last modified: October 28, 2022
