SQL Server SUM() Function

The SUM() function in SQL Server is a type of aggregate function. We use this function to calculate the total summed value in a set of values of a specified column or expression.

It is a built-in function, which accepts a single parameter that can be a column or valid expression and returns a single result to summarize the input data set. This function ignores the NULL values. However, it returns NULL when the result set does not have any rows. It can also work with the WHERE, GROUP BY, ORDER BY, and HAVING clauses to get the filtered result.

Syntax

The syntax of a SUM() function in SQL Server are given below:

SELECT SUM( DISTINCT aggregate_expression)  
FROM table_name(s)  
[WHERE conditions];

This function contains the following parameters:

aggregate_expression: It is the required expression that represents the column name we are going to calculate the sum.

table_name(s): It indicates the name of a table from where we want to retrieve columns. There can be specified more than one table.

WHERE conditions: It is an optional clause to define a condition that should satisfy the records to be selected.

DISTINCT: It tells the function to sum up only distinct values.

SUM() Function Example

Let us understand how the SUM() function works in SQL Server with the help of various examples. We are going to take the 'employee_info' table to demonstrate the SUM function practically. This table contains the following data:

Basic Example of SUM Function

This example explains the very basic use of a SUM function that returns the sum of all values in the specified column. Suppose we want to calculate the total number of salaries from the given table. We can write the query as below:

Executing the query will return the below output:

SUM Function with DISTINCT Clause

The DISTINCT keyword calculates only the unique data of the table. Suppose we want to calculate the sum of unique records present in the salary column of an employee_info table. We can write the query to do the same as below:

SELECT SUM( DISTINCT salary) AS "Total Salary" 
FROM employee_info;

Executing the query will return the following output that shows the sum of only unique values of the salary column:

SUM Function with WHERE Clause

The WHERE clause is used to filter the records of a table. This example will use the WHERE clause with the SUM() function to calculate the total salary of employees whose working hour is greater than 9.

SELECT SUM(salary) AS "Total Salary"
FROM employee_info 
WHERE working_hours>9;

When we execute the query, it returns the following result:

SUM Function with GROUP BY Clause

The GROUP BY clause is used to groups rows that have the same values into summary rows. It is very useful in the aggregate function to produce summary reports. We will get an error if we try to combine aggregate and non-aggregated columns in a SELECT statement as below:

SELECT occupation, SUM(salary) AS "Total Salary"
FROM employee_info;

Here is the error message:

However, if we use the GROUP BY clause, it does not throw an error. The below query will use the SUM() function to calculate the sum of employee's salary based on each occupation:

SELECT occupation, SUM(salary) AS "Total Salary"
FROM employee_info
GROUP BY occupation;

This query finds the employee associated with each occupation and sums up their total salary:

SUM Function with ORDER BY Clause

The ORDER BY clause is used to organize the table either in ascending or descending order. The below example will use the SUM() function with the ORDER BY clause, calculate the sum of employee's salary based on each occupation and then sorts the result set in the ascending order of total summed salary:

SELECT occupation, SUM(salary) AS "Total Salary"
FROM employee_info
GROUP BY occupation
ORDER BY SUM(salary);

Executing the statement will display the below output:

SUM Function with HAVING Clause

The HAVING clause is used with the GROUP BY clause to filter the grouped rows. This example uses the HAVING clause with the SUM() function, calculate the sum of an employee's salary based on each occupation and then check whether the aggregated salary is greater than 50000 or not. If this is true, the corresponding records displayed in the ascending order of total summed salary:

SELECT occupation, SUM(salary) AS "Total Salary"
FROM employee_info
GROUP BY occupation
HAVING SUM(salary)>50000
ORDER BY SUM(salary); 

Executing the statement will return the below output:

SUM Function with OVER()

The OVER() clause determines which rows from the query are applied to the function, in what order the function evaluates them, and when the function's calculations should restart. The below query provide a cumulative total sales for each occupation in the employee_info table:

SELECT DISTINCT occupation,
SUM(salary) OVER (PARTITION BY occupation) AS "Total Salary"
FROM employee_info; 

Executing the query will return the below result:

Conclusion

This article will explain a complete overview of how and when to use the SUM() function in SQL Server. We must be careful while using this function as it works with numeric data types only. Here we have discussed all the use cases of SUM function with various examples such as how to get the total summed values from the table using the WHERE, GROUP BY, HAVING, and OVER clauses.

Next TopicSQL Server AVG Function

← prev next →