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) 


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()

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.

FROM Employee
GROUP BY DepartmentID

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

SELECT *, salary + (100.5) AS NewSalary FROM Employee;
Want to check how much you know SQL Server?