What is SUM function in SQL?

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

SELECT SUM(column_name) FROM table_name WHERE condition;
Parameters Description
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.

Query:

SELECT SUM(emp_sal_paid) As Total_Paid

FROM tbl_emp_salary_paid

SQL SUM

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

SQL SUM count

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

SQL SUM Distinct

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

SQL SUM group by

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!