SQL SELECT SUM - Teradata Point

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.

Post Views: 853

Syntax

SQL SELCT SUM Example

#2. Using SQL DISTINCT example

#3. Using SQL GROUP BY example

You may also like this!