Oracle SUM function

The Oracle/PLSQL SUM function returns the total value of the expression.

Oracle/PLSQL syntax of SUM function

SELECT SUM(aggregate_expression_id)
FROM tabs
[WHERE conds]

OR syntax for SUM function with results grouped by one or more columns:

SELECT expression1_id, expression2_id, ... expression_n_id,
SUM(aggregate_expression_id)
FROM tabs
[WHERE conds]
GROUP BY expression1_id, expression2_id, ... expression_n_id;

Parameters and arguments of the function

• expression1_id, expression2_id, … expression_n_id – expressions that are not encapsulated in the SUM function and must be included in the GROUP BY operator at the end of the SQL query.
• aggregate_expression_id – is a column or expression to be summed up.
• tabs – tables, from which you want to get records. At least one table must be specified in FROM operator.
• WHERE conds – optional. These are the conditions that must be met for the selected records.

The SUM function returns a numeric value.

SUM function can be used in the following versions of Oracle/PLSQL

One Field Example

Let’s consider some examples of SUM function and learn how to use SUM function in Oracle/PLSQL.

For example, you may want to know what the total aggregate salary of all employees, whose salary exceeds $ 50,000 per year.

SELECT SUM(salary_id) AS "Total Salary".
FROM empls
WHERE salary_id > 50,000;

In this example of the SUM function we used the nickname “Total Salary” for SUM(salary_id). As a result, “Total Salary” will be displayed as a field name when returning the resulting set.

Example – using DISTINCT

You can use DISTINCT operator in SUM function. For example, the following SQL operator returns the total total salary with unique values of salaries, where the salary exceeds $ 50,000 per year.

SELECT SUM(DISTINCT salary_id) AS "Total Salary"
FROM empls
WHERE salary_id > 50,000;

If the salary were $80,000 per year, only one of these values would be used in the SUM function.

Example – using a formula

The expression contained in the SUM function does not have to be a single field. You can also use a formula. For example, you can calculate the total commission.

SELECT SUM(sales * 0.05) AS "Total Commission"
FROM ords;

Example – using GROUP BY

In some cases, you will need to use SUM function in GROUP BY operator.

For example, you could also use the SUM function to return the name of department and SUM(sales) (total sales in the corresponding department).

SELECT depart,
SUM(sales) AS "Total sales"
FROM order_details
GROUP BY depart;

Since your SELECT operator has one column that is not encapsulated in the SUM function, you must use the GROUP BY operator. Therefore the department field must be specified in the GROUP BY section.

The SUM Function (Introduction to Oracle SQL)