FB

Oracle SUM FunctionIn this article, I’m going to explain the Oracle SUM function, how it can be used, and show some examples.

Purpose of the Oracle SUM Function

The Oracle SUM function sums or adds up the values specified in the expression.

Like several other Oracle functions, you can use it as an aggregate or analytic function.

Most of the time, you’ll probably use this as an aggregate function, but I have explained both methods in this article.

It’s also a standard ANSI SQL function, so if you’ve used SUM on SQL Server or MySQL, you might already be familiar with it.

However, I’ll be looking at the Oracle version of it.

 

Syntax

Just like with the MAX function, you can use the SUM function as an aggregate function or an analytic function.

The syntax of Oracle SUM as an aggregate function is:

SUM ( [DISTINCT/ALL] expr)

The syntax of Oracle SUM as an aggregate function is:

SUM ( [DISTINCT/ALL] expr) OVER (analytic_clause)

Almost every time I’ve seen SUM used has been as an aggregate function.

 

Parameters

The parameters of the SUM function are:

  • expr (mandatory): The expression that is being included or looked at for the SUM function. This can be a column name, a set of values, or any other kind of valid Oracle expression.
  • analytic_clause (optional): This is used when you’re using the SUM function as an analytic function. It works similar to grouping your SUM function, but it’s a bit different.

What Does Oracle SUM OVER or Oracle SUM OVER PARTITION Do?

There is a clause on the SUM function called OVER. This allows you to use it as an analytic function.

What does that even mean?

It means you can perform the SUM function on a group of records, separate to a GROUP BY statement.

For example, you may want to list all employees, their salary, and the SUM of the salary values for all employees in their department. This is what the OVER clause can let you do.

 

Can You SUM DISTINCT Values in Oracle?

Yes, you can SUM the DISTINCT values.

SELECT SUM(DISTINCT col) would SUM the distinct values of col. This may be what you are looking for.

If you want to SUM one column based on the distinct values of another column, you may want to use a subquery to get the distinct values, and then perform a SUM.

See the Examples section for an example on how to use Oracle SUM with distinct values.

 

Do You Need To Use GROUP BY with Oracle SUM?

Not always. You only need to use it if you are selecting fields that are not included in aggregate function.

So, if you are just using SELECT SUM(col), then you don’t need a group by.

If you are using SELECT col1, SUM(col2) then you need to group by col1.

 

Can You SUM Multiple Columns?

Yes, you can perform the SUM function on multiple columns.

You can include them within the SUM function, such as SUM(col1 + col2).

See the Examples section below for more information.

 

Examples of the Oracle SUM Function

Here are some examples of the SUM function. I find that examples are the best way for me to learn about code, even with the explanation above.

First, let’s have a look at the STUDENT table which we’ll be using in this example.

SELECT * FROM student;

Result:

STUDENT_ID FIRST_NAME LAST_NAME FEES_REQUIRED FEES_PAID ENROLMENT_DATE
1 John Smith 500 100 01/FEB/2015
2 Susan Johnson 150 150 12/JAN/2015
3 Tom Capper 350 320 06/MAR/2015
4 Mark Holloway 500 410 20/JAN/2015
5 Steven Webber 100 80 09/MAR/2015
6 Julie Armstrong 100 0 12/FEB/2015
7 Michelle Randall 250 23/JAN/2015
8 Andrew Cooper 800 400 04/MAR/2015
9 Robert Pickering 110 100 30/JAN/2015
10 Tanya Hall 150 150 28/JAN/2015

 

Example 1 – Simple SUM

This example shows a simple SUM function on the entire table

SELECT SUM(fees_paid) AS SUM_FEES_PAID
FROM student;

Result:

SUM_FEES_PAID
1710

This is the total of all fees_paid values in the table.

 

Example 2 – SUM with WHERE

This example uses the WHERE clause to only shoe the SUM of fees_paid where it is not equal to fees_required.

SELECT SUM(fees_paid) AS SUM_FEES_PAID
FROM student
WHERE fees_paid <> fees_required;

Result:

SUM_FEES_PAID
1410

 

Example 3 – SUM with GROUP BY

This example shows how to use the SUM function with GROUP BY.

SELECT EXTRACT(MONTH FROM enrolment_date) AS ENROLMENT_MONTH,
SUM(fees_paid) AS SUM_FEES_PAID
FROM student
GROUP BY EXTRACT(MONTH FROM enrolment_date);

Result:

ENROLMENT_MONTH SUM_FEES_PAID
1 810
2 100
3 800

It uses the EXTRACT function to get the enrolment month, and then group by this value. It finds the SUM of fees_paid for each enrolment month.

 

Example 4 – SUM with a Formula

This example uses a formula within the SUM function to find out how much tax is needed, based on a 10% tax rate.

SELECT SUM(fees_paid) AS SUM_FEES_PAID,
SUM(fees_paid * 0.1) AS SUM_TAX
FROM student;

Result:

SUM_FEES_PAID SUM_TAX
1710 171

 

Example 5 – SUM with Multiple Columns

This example uses the SUM function on multiple columns

SELECT SUM(fees_required - fees_paid) AS FEES_OWING
FROM student;

Result:

FEES_OWING
1050

 

Example 6 – SUM with OVER, or SUM as an Analytic Function

This example uses the SUM OVER PARTITION BY to show how SUM can be used as an analytic function.

SELECT
first_name,
last_name,
EXTRACT(MONTH FROM enrolment_date) AS ENROLMENT_MONTH,
SUM(fees_paid) OVER (PARTITION BY (EXTRACT(MONTH FROM enrolment_date))) AS FEES_MONTH
FROM student;

Result;

FIRST_NAME LAST_NAME ENROLMENT_MONTH FEES_MONTH
Robert Pickering 1 810
Susan Johnson 1 810
Mark Holloway 1 810
Tanya Hall 1 810
Michelle Randall 1 810
John Smith 2 100
Julie Armstrong 2 100
Andrew Cooper 3 800
Tom Capper 3 800
Steven Webber 3 800

The SUM value is the sum of all fees_paid for the enrolment month, as shown earlier. It is shown for each student, so it is repeated several times.

 

Example 7 – SUM DISTINCT

This example shows how you can use the SUM function with DISTINCT.

SELECT SUM(fees_paid) AS SUM_ALL_FEES,
SUM(DISTINCT fees_paid) AS SUM_DISTINCT_FEES
FROM student;

Result:

SUM_ALL_FEES SUM_DISTINCT_FEES
1710 1460

The results show the SUM function with and without the DISTINCT keyword.

 

Similar Functions

Some functions which are similar to the SUM function are:

  • MIN – This function gets the lowest of the supplied expression. It’s the opposite of the MAX function.
  • MAX – This function gets the highest of the supplied expression. It’s the opposite of the MIN function.
  • AVERAGE – This function gets the average value of a supplied expression.
  • COUNT – This function counts the values supplied.

 

You can find a fill list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Image courtesy of digitalart / FreeDigitalPhotos.net

Get Your SQL Function Cheat Sheet Now: