Oracle / PLSQL: SUM Function

This article is written about how to use the Oracle/PLSQL SUM function with syntax and examples.

Description

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

Syntax

The syntax for the SUM characteristic in Oracle/PLSQL is:

SELECT SUM(aggregate_expression)
FROM tables
[WHERE conditions];

OR the syntax for the SUM characteristic when grouping the effects via one or more columns is:

SELECT expression1, expression2, ... expression_n,
       SUM(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

Parameters or Arguments

expression1, expression2, … expression_n Expressions that are not encapsulated within the SUM function and ought to be covered in the GROUP BY clause at the stop of the SQL statement. aggregate_expression This is the column or expression that will be summed. tables The tables that you desire to retrieve documents from. There need to be at least one desk listed in the FROM clause. WHERE conditions Optional. These are conditions that should be met for the archives to be selected.

Returns

The SUM function returns a numeric value.

Applies To

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

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example – With Single Field

Let’s seem at some Oracle SUM characteristic examples and explore how to use the SUM function in Oracle/PLSQL.

For example, you would possibly want to know how the blended total earnings of all personnel whose income is above $50,000 / year.

SELECT SUM(salary) AS "Total Salary"
FROM employees
WHERE salary > 50000;

In this SUM characteristic example, we’ve got aliased the SUM(salary) expression as “Total Salary”. As a result, “Total Salary” will show as the discipline title when the result set is returned.

Example – Using DISTINCT

You can use the DISTINCT clause inside the SUM function. For example, the SQL declaration under returns the mixed complete earnings of special revenue values the place the revenue is above $50,000 / year.

SELECT SUM(DISTINCT salary) AS "Total Salary"
FROM employees
WHERE salary > 50000;

If there had been two salaries of $82,000/year, only one of these values would be used in the SUM function.

Example – Using Formula

The expression contained inside the SUM characteristic does now not want to be a single field. You may want to additionally use a formula. For example, you might want to calculate the complete commission.

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

Example – Using GROUP BY

In some cases, you will be required to use the GROUP BY clause with the SUM function.

For example, you could additionally use the SUM feature to return the identify of the branch and the complete income (in the associated department).

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

Because you have listed one column in your SELECT declaration that is no longer encapsulated in the SUM function, you should use a GROUP BY clause. The branch discipline must, therefore, be listed in the GROUP BY section.