SQL SUM Function

SQL SUM FunctionSQL SUM Function returns total or summed up value of the available records.Oracle SUM Function can accept a column or field of database table or a formula as parameter.


SQL SUM Function Syntax

SELECT SUM(numeric column / formula)
FROM table_name
WHERE conditions;

SQL SUM Function Examples

Suppose we have a table named “Employee” with the data as shown below.

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Now we will see what Oracle SUM Function does when used in different scenarios


SQL SUM Function – SQL SELECT SUM Example

Suppose we wish to view total ‘Salary’ of all employees from ‘employee’ table.

SQL SELECT SUM query below will return total salary given to all employees.

 SELECT SUM(salary) Total_Salary
 FROM employee;

SQL SELECT SUM Function query above returns ‘120,000’ because total salaries of all employees is ‘120,000’.

Note: SQL SELECT SUM Function query above uses “Total_Salary” as alias name for SUM(Salary) query result column.


SQL SUM Function -Using Formula Example

SQL SUM Function can also accept a formula as parameter for summing up values.

For example, Oracle SUM Function query below returns total commission of employees from ‘Sales’ department.

 SELECT SUM(salary*(commission/100)) Commission_Amount
 FROM employee
 WHERE department  = 'Sales';

SQL SUM Function query above returns ‘4200’ as commission of ‘Sales’ department.

Note: Oracle SUM Function query above also uses the SQL WHERE Clause.


SQL SUM Function -SQL SUM Group By Example

SQL SUM Function can be used with SQL GROUP BY Clause to form SQL SUM Group By query.

For example, SQL SUM Group By Function query below returns total salary for each department.

 SELECT department, SUM(salary) Total_Salary
 FROM employee
 GROUP BY department;

SQL SUM Group By query above returns following data:

Department Total_Salary
Sales 42000
IT 48000
Support 30000

By using the SQL Group By Clause we have fetched the total salary of every unique department.


Tagged , , , , , , , , , , , . Bookmark the permalink.