SQL SELECT SUM

Learning Objective: In this SQL tutorial, you will learn how to use SQL SELECT SUM function with syntax and examples.

The SQL SELECT SUM function is used to return sum of the expression mentioned in the SELECT statement.

Syntax

The syntax for SQL SELECT SUM function is as follows:

SELECT SUM(aggregate_expression)
FROM tables
[WHERE conditions];

OR the syntax for the SQL SUM function when grouping the results on one or more expression:

SELECT expression1, expression2, ... expression_n,
SUM(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ...,expression_n;

In this syntax,

• expression1, expression2, …, expression_n – Expressions or columns that are not part of the sum function must be included in the GROUP BY clause at the end of the SQL statement.
• aggregate_expression – Expressions or columns that will be summed.
• tables – The name of the table from where you want to retrieve the records. There should be at least one table in the FROM clause.
• WHERE conditions – Optional. These are the conditions need to be met by the records to be part of the result set.

SQL SELCT SUM Example 

#1) Using Single Expression example

Let’s consider a Employee table as below:

For example, you want to know the total salary of the employees whose salary is above 40000.

SELECT SUM(emp_salary) AS "Total Salary"
FROM Employee 
WHERE emp_salary > 40000;

In the above SQL SUM function, we have aliased SUM(emp_salary) expression as “Total Salary”. As a result, “Total Salary” will be displayed in the result set.

#2. Using SQL DISTINCT example

You can use SQL DISTINCT clause within the SQL SUM function. For example, the below query returns the total unique salary where the unique salary is more than 40000.

SELECT SUM(DISTINCT emp_salary) AS "Total Salary"
FROM Employee 
WHERE emp_salary > 40000;

In the table, two employees have salary of 45000 and another two employees have salary of 42000.  In the above example, only one salary has been selected from the above two scenario.

#3. Using SQL GROUP BY example

Sometimes, you will be required to use SQL GROUP BY clause with the SQL SUM function.

For example, if you want to total salary whose location are “Kolkata” you can use the below SQL query.

SELECT emp_location as "Employee Location", SUM(DISTINCT emp_salary) AS "Total Salary"
FROM Employee 
GROUP BY "Employee Location";

In the above example, you have listed one column which is not included in the SUM function so it is required to use SQL GROUP BY clause. Therefore the column emp_location must be included in the GROUP BY clause.

Summary: In this tutorial, you have learned how to use SUM function in SQL query.