SQL SELECT SUM

The SQL Sum() function is an aggregate function in SQL that returns the total values of an expression. The expression may be numerical, or it may be an expression.

Syntax:

SELECT SUM(columnname) FROM table_name WHERE conditions; 

Consider the existing tables which have the following records:

Table: Employees

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4

Example 1: Write a query that sums the total salary of employees from the employees' table.

SELECT SUM (SALARY) AS 'SALARY' FROM EMPLOYEES;

The Sum expression will display the sum of the total salary. s

Output:

SALARY

742000

Example 2: Write a query to sum the salary of employees whose city is Pune from the employees' table.

SELECT CITY, SUM(SALARY) AS 'SALARY'  FROM EMPLOYEES WHERE CITY = 'PUNE';

This query will make a sum of the salary of the employees whose city is Pune.

Output:

CITY	SALARY
PUNE	186500

Example 3: Write a query to sum the employees' salary from the employees' table of employees whose department is Oracle.

SELECT DEPARTMENT, SUM(SALARY) AS 'SALARY'  FROM EMPLOYEES WHERE DEPARTMENT = 'ORACLE';

Output:

DEPARTMENT	SALARY
ORACLE	181000

Example 4: Write a query to sum salary of employees from the employees' table whose department includes oracle and FMW.

SELECT SUM(SALARY) AS 'SALARY'  FROM EMPLOYEES WHERE DEPARTMENT IN ('ORACLE', 'FMW');

Output:

SALARY

347500

Example 4: Write a query to summed salary of employees from employees’ table whose salary is greater than 50000 and city includes Pune, and Mumbai.

SELECT CITY, SUM(SALARY) AS EMPLOYEE_SALARY FROM EMPLOYEES WHERE SALARY > 50000 AND CITY IN ('PUNE', 'MUMBAI') GROUP BY CITY;

Output:

CITY	EMPLOYEE_SALARY
PUNE	119000
MUMBAI	186500

Example 5: Write a query to sum salary of employees from employees' table whose salary is greater than 50000 or city includes Oracle, FMW and group by the department.

SELECT DEPARTMENT, SUM(SALARY) AS EMPLOYEE_SALARY FROM EMPLOYEES WHERE SALARY > 50000 OR DEPARTMENT IN ('ORACLE', 'FMW') GROUP BY DEPARTMENT;

Output:

DEPARTMENT	EMPLOYEE_SALARY
C#	60000
FMW	166500
JAVA	168500
ORACLE	181000
TESTING	116000

Example 6:  Write a query to sum the salary of employees from employee's table using unique cities of employees and group by city.

SELECT CITY, SUM(DISTINCT SALARY) AS 'EMPLOYEE_SALARY' FROM EMPLOYEES GROUP BY CITY;

Output:

CITY	EMPLOYEE_SALARY
CHANDIGARH	55500
DELHI	115500
JAIPUR	50500
MUMBAI	169000
NOIDA	114500
PUNE	121000

Example 7:  Write a query to sum the salary of employees from employee's table which have unique department and group by the department.

SELECT DEPARTMENT, SUM(DISTINCT SALARY) AS 'EMPLOYEE_SALARY' FROM EMPLOYEES GROUP BY DEPARTMENT;

Output:

DEPARTMENT	EMPLOYEE_SALARY
C#	110000
FMW	116000
JAVA	168500
ORACLE	181000
TESTING	116000

Example 8:  Write a query to sum the salary of employees from employee's table and group by city, department.

SELECT CITY, DEPARTMENT, SUM(SALARY) AS 'EMPLOYEE_SALARY' FROM EMPLOYEES GROUP BY CITY, DEPARTMENT;

Output:

CITY	DEPARTMENT	EMPLOYEE_SALARY
CHANDIGARH	ORACLE	55500
DELHI	ORACLE	60000
DELHI	TESTING	55500
JAIPUR	FMW	101000
MUMBAI	C#	50000
MUMBAI	JAVA	58500
MUMBAI	TESTING	60500
NOIDA	C#	60000
NOIDA	JAVA	54500
PUNE	FMW	65500
PUNE	JAVA	55500
PUNE	ORACLE	65500

Example 9: Write a query to sum the salary of the employees from employee's table group by the city where aggregate salary is greater than 75000.

SELECT CITY, SUM(SALARY) AS SALARY FROM EMPLOYEES GROUP BY CITY HAVING SUM(SALARY) > 75000;

Output:

CITY	SALARY
DELHI	115500
JAIPUR	101000
MUMBAI	169000
NOIDA	114500
PUNE	186500

As we can see, it has only cities that aggregate salary is greater than 75000.

Example 10: Write a query to sum an employee's salary with a laptop and group by the department.

SELECT DEPARTMENT, SUM(SALARY) AS SALARY FROM EMPLOYEES WHERE EMPLOYEEID IN (SELECT EMPLOYEEID FROM LAPTOP) GROUP BY DEPARTMENT;

Output:

DEPARTMENT	SALARY
C#	60000
JAVA	113000
ORACLE	60000
TESTING	55500

Example 11: Write a query to sum employees' salary with laptop and  group by the department where aggregate salary is greater than 58000.

SELECT DEPARTMENT, SUM(SALARY) AS SALARY FROM EMPLOYEES WHERE EMPLOYEEID IN (SELECT EMPLOYEEID FROM LAPTOP) GROUP BY DEPARTMENT HAVING SUM(SALARY) > 58000;

Output:

DEPARTMENT	SALARY
C#	60000
JAVA	113000
ORACLE	60000