SQL Server SUM() Function: Get Total

In SQL Server, the SUM() function returns the sum of all or distinct values in a given expression. It can only be used with the numeric type column or expression. The NULL values are ignored.

SUM ( [ ALL |  DISTINCT ]  numeric_expression)

Parameters

numeric_expression: This is the input numeric value. It can be a constant, a table column, or a function

ALL: This specifies that the sum considers all values. This is the default value.

DISTINCT: This applies to only distinct values in the numeric_expression. Unique values are considered.

Return Value

Expression	Return type
Tinyint	int
Smallint	int
Int	int
Bigint	bigint
decimal category (p, s)	decimal(38, max(s,6))
money and smallmoney category	money
float and real category	float

Let's use the SUM() function with the following Employee table.

The following calculates the total of salaries of all the employees.

Example: SUM()

SELECT SUM(SALARY) FROM Employee;

The SUM() is an aggregate function, so it cannot be used with columns which are not in the group by clause. For example, you cannot take any other column in the SELECT query with the SUM(SALARY). It will raise an error.

Use the DepartmentID with the GROUP BY clause to use the SUM() function with the it, as shown below.

Example:

SELECT DepartmentID, SUM (SALARY) 
FROM Employee
GROUP BY DepartmentID

Use the + operator to add numeric value to column, as shown below.

Example:

SELECT *, salary + (100.5) AS NewSalary FROM Employee;