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.
The syntax of a SUM() function in SQL Server are given below:
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:
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.
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:
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:
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:
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:
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:
Executing the query will return the below result:
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.