SQL SUM Function In Actions

Summary: in this tutorial, you will learn how to use the SQL SUM function to calculate the sum of all or distinct values.

Introduction to the SQL SUM function #

The 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)Code language: SQL (Structured Query Language) (sql)

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 following 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) total_salary
FROM
  employees;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 total_salary
--------------
    322400.00Code language: CSS (css)

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) total_salary
FROM
  employees
WHERE
  department_id = 5;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 total_salary
--------------
     41200.00Code language: CSS (css)

Using SUM function 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) total_salary
FROM
  employees
GROUP BY
  department_id
ORDER BY
  total_salary;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 department_id | total_salary
---------------+--------------
             1 |      4400.00
             4 |      6500.00
             7 |     10000.00
             2 |     19000.00
            11 |     20300.00
             3 |     24900.00
             6 |     28800.00
             5 |     41200.00
            10 |     51600.00
             8 |     57700.00
             9 |     58000.00

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

SELECT
  department_name,
  SUM(salary) total_salary
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
  department_name
ORDER BY
  total_salary;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 department_name  | total_salary
------------------+--------------
 Administration   |      4400.00
 Human Resources  |      6500.00
 Public Relations |     10000.00
 Marketing        |     19000.00
 Accounting       |     20300.00
 Purchasing       |     24900.00
 IT               |     28800.00
 Shipping         |     41200.00
 Finance          |     51600.00
 Sales            |     57700.00
 Executive        |     58000.00Code language: PHP (php)

Using 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
  department_name,
  SUM(salary) total_salary
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
  department_name
HAVING
  SUM(salary) > 30000
ORDER BY
  total_salary;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 department_name | total_salary
-----------------+--------------
 Shipping        |     41200.00
 Finance         |     51600.00
 Sales           |     57700.00
 Executive       |     58000.00

Summary #

Use the SUM function to calculate the sum of values in a set.
Use the SUM function with the GROUP BY clause to calculate sum for each group.

Databases #

Was this tutorial helpful ?