ORACLE SUM

SUM is an aggregate function that returns the sum total of all values in a set.

Syntax

The syntax of the SUM function is as follows:

SELECT SUM(expression)
FROM table
[WHERE condition];

Here,

expression – It is a set of values or a column name. By default SUM function calculates sum of all values, but if we want the sum of only distinct values then we use the DISTINCT clause before expression.

condition – Optional. We can give a condition to further sort the results.

Let’s see the difference when we use DISTINCT and ALL clause before the expression:

SUM( 1, 2, 3, 3) = 9

SUM DISTINCT ( 1,2, 3, 3) = 6

SUM like all other aggregate functions omits or ignores the NULL values.

Examples

We will be using the Employee table from HR schema for the following queries:

1. Simple SUM Query

We use the following query for finding the sum of salary of all employees in department_id=60.

SELECT SUM( salary )
FROM employees
WHERE department_id = 60;

Result:

The SUM function returns the sum of salary of each employee in department=60.

2. SUM with GROUP BY Function

We use the following query to find the sum of salary of all employees in each department.

SELECT department_id, SUM( salary )
FROM employees
GROUP BY
department_id
ORDER BY
SUM( salary ) DESC;

Result:

The query returns the sum of salaries in each department.

Suppose, we want to find the Total salary of employees which is the salary + salary * commission_percentage. But since, not every employee gets a commission there are a lot of NULL values in Commission_pct column. For replacing those, NULL values with zero we use NVL function.

Lets see the following query, which helps us calculate the total salary of employees in each department after adding the commission.

SELECT department_id,
SUM( salary + salary * NVL(commission_pct,0)) "Total Salary"
FROM employees
GROUP BY
department_id
ORDER BY
SUM( salary ) DESC;

Result:

The query returns the total salary of employees in each department.

3. SUM with HAVING Clause

Suppose, we want to find the departments where the sum of salary is within a specific range, then we use the HAVING clause.

SELECT department_id,
SUM(salary)
FROM employees
GROUP BY
department_id
HAVING
SUM(salary ) BETWEEN 20000 AND 100000
ORDER BY
SUM( salary ) DESC;

Result:

The query returns all the departments where the sum of salary is in the range of 20000 to 100000.

4. SUM with INNER JOIN Clause

Let’s see a query, where we use inner join to join the Employee and Departments table and for showing the department name in the query that we saw above:

SELECT department_name,SUM( salary)
FROM employees
INNER JOIN departments USING(department_id)
GROUP BY department_name
HAVING
SUM(salary ) BETWEEN 20000 AND 100000
ORDER BY
SUM( salary) DESC;

Result: