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.
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.
SELECT DepartmentID, SUM (SALARY)
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;