SQL SUM
Summary: 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
the table below for 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;
In this tutorial, you have learned how to use the SUM function to calculate the sum of values in a set.
0 Comments
CAN FEEDBACK
Emoji