SQL 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.
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:
By using the SQL Group By Clause we have fetched the total salary of every unique department.