Oracle SUM

Created OnJanuary 8, 2020

Introduction

In this tutorial, we are going to explain how to use Oracle SUM function with basic syntax and many examples for better understanding.

SUM function returns the sum of expression or columns.

Syntax:

SUM(aggregation_column)
-- OR
column1, column2, ... column_n, SUM(aggregate_column)

Name	Description
column_n	A column that is not encapsulated within the SUM function and must be included in the GROUP BY clause at the end of the SQL statement.
aggregation_column	A column/s that you want to sum up.

Note:

If column_n is specified, then we must include all columns listed in a group by clause.
group by clause could have columns are not selected in select clause, but not vice-versa means all columns available in select clause must be available in a group by clause except aggregated column.

Examples:

Let’s get some examples about Oracle SUM and how to use it:

-- Single field aggregated
SELECT SUM(salary) AS "Total Salary"
FROM employees
WHERE salary > 10000;

-- Using DISTINCT with aggregated column
SELECT SUM(DISTINCT salary) AS "Total Salary"
FROM employees
WHERE salary > 10000;

In this case, a DISTINCT clause will eliminate duplicate same salaries. eg, We have two salaries with the same value as “11000”. aggregation function will sum up only one salary with value 11000 and so on.

-- Using GROUP BY Clause
SELECT   department_id, sum (salary) AS "Total salary Per Depts"
FROM     employees
WHERE    department_id IS NOT NULL
GROUP BY department_id;

Here in this example we’re getting department_id and not captured in aggregation function, so department column should be listed in the GROUP BY clause.

-- Using HAVING Clause
SELECT   department_id, sum (salary) AS "Total salary Per Depts"
FROM     employees
WHERE    department_id IS NOT NULL
GROUP BY department_id
HAVING   department_id IN (30, 90, 70);