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:

emp_id emp_name emp_gender emp_salary dept_code emp_location
1510 Avinash Sirsath M 33000 101 Mumbai
1511 Ramjan Ali M 56000 105 Kolkata
1512 Piyanka Saha F 35000 103 Kolkata
1513 Piyam mondal M 42000 101 Kolkata
1514 Jitu Garg M 43000 104 Mumbai
1515 Raman Sahani M 49000 105 Delhi
1516 Lata Malhotra F 27000 104 Mumbai
1517 Prakash Javedkar M 45000 101 Delhi
1518 Nitu Rani F 45000 103 Mumbai
1519 Pallab Das M 42000 103 Kolkata
1520 Pankaj Sharma M 55000 101 Bangalore

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.

Total Salary
367000

#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.

Total Salary
280000

#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";
Employee Location Total Salary
Kolkata 175000

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.

Leave a Comment