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*and**partition_by_clause**is for using this function on a specific partition.**order_by_clause**divides the result set produced by the FROM clause into partitions to which the function is applied**partition_by_clause**

determines the logical order in which the operation is performed.**order_by_clause**

To demonstrate the usage of the SQL SUM function, I have created a table named ** Employee**. It has 5 columns –

**,**

*name***,**

*salary***,**

*rent***, and**

*tax***. The following use cases will be in the focus:**

*employment nature*- 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

**column to get the total salary of each type of employee categorized under their employment nature.**

*Salary*```
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

**expenses of employees. Then, I applied**

*tax***GROUP BY**and

**HAVING**statements on the

**column. In simple words, I wanted to know the total rent and tax expenses spent by employees with a salary of $150000 or more.**

*Salary*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

**) as their total expenses. Then is applies**

*tax***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: September 27, 2021