SQL SUM

In this tutorial, we will introduce you to the SQL SUM function that calculates the sum of all or distinct values.

Introduction to the SQL SUM function

The SQL SUM function is an aggregate function that returns the sum of all or distinct values. We can apply the SUM function to the numeric column only.

The following illustrates the syntax of the SUM function.

SUM([ALL|DISTINCT] expression)

The ALL operator allows you to apply the aggregate to all values. The SUM function uses the ALL operator by default.

For example, if you have a set of (1,2,3,3,NULL). The SUM function returns 9. Note that the SUM function ignores NULL values.

To calculate the sum of unique values, you use the DISTINCT operator e.g., the SUM(DISTINCT) of the set (1,2,3,3,NULL) is 6.

SQL SUM function examples

We will use the employees table for the demonstration purposes.

To get the sum of salaries of all employees, we apply the SUM function to the salary column as the following query:

SELECT 
    SUM(salary)
FROM
    employees;

To calculate the sum of salaries of employees who work in department id 5, we add a WHERE clause to the query above as follows:

SELECT 
    SUM(salary)
FROM
    employees
WHERE
    department_id = 5;

SQL SUM with GROUP BY clause example

We often use the SUM function in conjunction with the GROUP BY clause to calculate the sums of groups.

For example, to calculate the sum of salaries of employees for each department, we apply the SUM function to the salary column and group the rows by department_id column as the following query:

SELECT
    department_id,
    SUM(salary)
FROM
    employees
GROUP BY
    department_id;

To include the department name in the result set, we join the employees table with the departments table as follows:

SELECT
    e.department_id,
    department_name,
    SUM(salary)
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id;

SQL SUM with ORDER BY clause example

Suppose we want to have the sums of salaries of departments sorted in descending order, in this case, we use the SUM function in the ORDER BY clause as follows:

SELECT
    e.department_id,
    department_name,
    SUM(salary)
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id
ORDER BY
    SUM(salary) DESC;

SQL SUM function with HAVING clause example

To filter groups based on condition, you use the HAVING clause. If you want to filter the groups based on the result of the SUM function, you have to place the SUM function in the GROUP BY clause.

For example, if you want to get the departments and their sums of salaries greater than 3000, you use the following statement:

SELECT
    e.department_id,
    department_name,
    SUM(salary)
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id
HAVING
    SUM(salary) > 30000
ORDER BY
    SUM(salary) DESC;