The SQL SUM is an aggregate function that is used to return the sum of a numeric column for all the values or DISTINCT values.
SELECT SUM(salary) FROM employees; SELECT SUM(sales) FROM orders WHERE year = 2023; SELECT SUM(salary) AS total_salary FROM employees; SELECT department, SUM(salary) FROM employees GROUP BY department;
If NULL values are found in the given numeric column, these are ignored by the SUM function.
Syntax for using the SUM function
|column_name||The numeric column or expression for which you want to calculate the sum.|
|table_name||The name of the table from which to sum the values.|
|condition||The condition is optional. This is applied in the WHERE or HAVING clause.|
The SUM function example with Select
For the example, have a look at the table in the graphic below. The tbl_emp_salary_paid stores the salaries paid to each employee every month (for the demo only).
By using the SUM function, we will get the total amount paid to all employees without using WHERE clause.
SELECT SUM(emp_sal_paid) As Total_Paid FROM tbl_emp_salary_paid
The example of using COUNT and SUM functions together
What if you want to know the count of rows for what the sum is calculated?
For example, in our employee salaries table, we want to know the number of times salary was paid along with the sum.
The query and the result are as follows from the demo table:
SELECT SUM(emp_sal_paid) As Total_Salary, COUNT(*) As Salaries_Count FROM tbl_emp_salary_paid where emp_sal_paid > 3500
Using SUM with DISTINCT clause
As mentioned in the introductory part, you may get the sum of distinct values by using the SUM function. The following query shows how to use the DISTINCT clause with SUM SQL function for that:
SELECT SUM(DISTINCT emp_sal_paid) AS "Unique Salary Sum" from tbl_emp_salary_paid
The example of using GROUP BY clause with the SUM function
What about getting the sum of salaries paid to each employee separately rather than the accumulative sum of the complete column?
The answer to this is using the SQL GROUP BY clause with the SUM function.
See the following query and resultant table graphic:
SELECT emp_name As "Employee Name", SUM(emp_sal_paid) As "Total Sal Paid" FROM tbl_emp_salary_paid Group By emp_name