SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAC#5
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4

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
SQL SELECT SUM

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:

CITYSALARY
PUNE186500
SQL SELECT SUM

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:

DEPARTMENTSALARY
ORACLE181000
SQL SELECT SUM

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
SQL SELECT SUM

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:

CITYEMPLOYEE_SALARY
PUNE119000
MUMBAI186500
SQL SELECT SUM

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:

DEPARTMENTEMPLOYEE_SALARY
C#60000
FMW166500
JAVA168500
ORACLE181000
TESTING116000
SQL SELECT SUM

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:

CITYEMPLOYEE_SALARY
CHANDIGARH55500
DELHI115500
JAIPUR50500
MUMBAI169000
NOIDA114500
PUNE121000
SQL SELECT SUM

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:

DEPARTMENTEMPLOYEE_SALARY
C#110000
FMW116000
JAVA168500
ORACLE181000
TESTING116000
SQL SELECT SUM

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:

CITYDEPARTMENTEMPLOYEE_SALARY
CHANDIGARHORACLE55500
DELHIORACLE60000
DELHITESTING55500
JAIPURFMW101000
MUMBAIC#50000
MUMBAIJAVA58500
MUMBAITESTING60500
NOIDAC#60000
NOIDAJAVA54500
PUNEFMW65500
PUNEJAVA55500
PUNEORACLE65500
SQL SELECT SUM

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:

CITYSALARY
DELHI115500
JAIPUR101000
MUMBAI169000
NOIDA114500
PUNE186500
SQL SELECT SUM

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:

DEPARTMENTSALARY
C#60000
JAVA113000
ORACLE60000
TESTING55500
SQL SELECT SUM

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:

DEPARTMENTSALARY
C#60000
JAVA113000
ORACLE60000
SQL SELECT SUM



ADVERTISEMENT
ADVERTISEMENT