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.
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.
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;
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;
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;
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);
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;
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;
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;
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;
The results show the SUM function with and without the DISTINCT keyword.
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