SQL SUM

SQL SUM

 

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.

Reactions

Post a Comment

0 Comments

close