{"id":160,"date":"2016-03-24T12:47:45","date_gmt":"2016-03-24T12:47:45","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=160"},"modified":"2025-01-18T21:48:51","modified_gmt":"2025-01-19T04:48:51","slug":"sql-sum","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-aggregate-functions\/sql-sum\/","title":{"rendered":"SQL SUM Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL SUM<\/code> function to calculate the sum of all or distinct values.<\/p>\n\n\n\n

Introduction to the SQL SUM function #<\/a><\/h2>\n\n\n\n

The SUM<\/code> function is an aggregate function<\/a> that returns the sum of all or distinct values. We can apply the SUM<\/code> function to the numeric column only.<\/p>\n\n\n\n

The following illustrates the syntax of the SUM<\/code> function.<\/p>\n\n\n

SUM([ALL|DISTINCT] expression)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The ALL<\/code> operator allows you to apply the aggregate to all values. The SUM<\/code> function uses the ALL<\/code> operator by default.<\/p>\n\n\n\n
For example, if you have a set of (1,2,3,3,NULL). The SUM<\/code> function returns 9. Note that the SUM<\/code> function ignores NULL values.<\/p>\n\n\n\n
SQL SUM function examples #<\/a><\/h2>\n\n\n\n
We will use the following employees<\/code> table for the demonstration purposes.<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
To get the sum of salaries of all employees, we apply the SUM<\/code> function to the salary column as the following query:<\/p>\n\n\n
SELECT<\/span>\n  SUM<\/span>(salary) total_salary\nFROM<\/span>\n  employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 total_salary<\/span>\n--------------<\/span>\n    322400.00<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span>\n  SUM<\/span>(salary) total_salary\nFROM<\/span>\n  employees\nWHERE<\/span>\n  department_id = 5<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 total_salary<\/span>\n--------------<\/span>\n     41200.00<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Using SUM function with GROUP BY clause example #<\/a><\/h3>\n\n\n\n
We often use the SUM<\/code> function in conjunction with the GROUP BY<\/a> clause to calculate the sums of groups.<\/p>\n\n\n\n
For example, to calculate the sum of salaries of employees for each department, we apply the SUM<\/code> function to the salary<\/code> column and group the rows by department_id<\/code> column as the following query:<\/p>\n\n\n
SELECT<\/span>\n  department_id,\n  SUM<\/span>(salary) total_salary\nFROM<\/span>\n  employees\nGROUP<\/span> BY<\/span>\n  department_id\nORDER<\/span> BY<\/span>\n  total_salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 department_id | total_salary\n---------------+--------------\n             1 |      4400.00\n             4 |      6500.00\n             7 |     10000.00\n             2 |     19000.00\n            11 |     20300.00\n             3 |     24900.00\n             6 |     28800.00\n             5 |     41200.00\n            10 |     51600.00\n             8 |     57700.00\n             9 |     58000.00<\/code><\/span><\/pre>\n\n\n
To include the department name in the result set, we join<\/a> the employees<\/code> table with the departments<\/code> table as follows:<\/p>\n\n\n
SELECT<\/span>\n  department_name,\n  SUM<\/span>(salary) total_salary\nFROM<\/span>\n  employees e\n  INNER<\/span> JOIN<\/span> departments d ON<\/span> d.department_id = e.department_id\nGROUP<\/span> BY<\/span>\n  department_name\nORDER<\/span> BY<\/span>\n  total_salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 department_name  | total_salary\n------------------+--------------\n Administration   |      4400.00<\/span>\n Human Resources  |      6500.00<\/span>\n Public<\/span> Relations |     10000.00<\/span>\n Marketing        |     19000.00<\/span>\n Accounting       |     20300.00<\/span>\n Purchasing       |     24900.00<\/span>\n IT               |     28800.00<\/span>\n Shipping         |     41200.00<\/span>\n Finance          |     51600.00<\/span>\n Sales            |     57700.00<\/span>\n Executive        |     58000.00<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Using SUM function with HAVING clause example #<\/a><\/h3>\n\n\n\n
To filter groups based on condition, you use the HAVING<\/a> clause. If you want to filter the groups based on the result of the SUM<\/code> function, you have to place the SUM<\/code> function in the GROUP BY<\/a> clause.<\/p>\n\n\n\n
For example, if you want to get the departments and their sums of salaries greater than 3000, you use the following statement:<\/p>\n\n\n
SELECT<\/span>\n  department_name,\n  SUM<\/span>(salary) total_salary\nFROM<\/span>\n  employees e\n  INNER<\/span> JOIN<\/span> departments d ON<\/span> d.department_id = e.department_id\nGROUP<\/span> BY<\/span>\n  department_name\nHAVING<\/span>\n  SUM<\/span>(salary) > 30000<\/span>\nORDER<\/span> BY<\/span>\n  total_salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 department_name | total_salary\n-----------------+--------------\n Shipping        |     41200.00\n Finance         |     51600.00\n Sales           |     57700.00\n Executive       |     58000.00<\/code><\/span><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
To calculate the sum of salaries of employees who work in department id 5, we add a WHERE clause<\/a> to the query above as follows:<\/p>\n\n\n
Try it<\/a><\/p>\n\n\n\n
To calculate the sum of unique values, you use the DISTINCT<\/a> operator e.g., the SUM(DISTINCT)<\/code> of the set (1,2,3,3,NULL) is 6.<\/p>\n\n\n\n