FB

SQL SUM Function Explained with Examples

The SQL SUM function can be used to add numbers together. Learn how to use it and see some examples in this article.


 

What is the SQL SUM Function?

The SUM function is used to add up the numbers in a specified column and return the total. It’s part of the SQL Standard so it’s available in all major databases (including Oracle, SQL Server, MySQL, and PostgreSQL).

It’s a common function when working with databases and is often one of the first that we learn about.

You can use it as an aggregate function, which adds all of the numbers together, or as an analytic function, which adds numbers for specific groups together. We’ll see examples of both in this article

 

SQL SUM Syntax

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

The syntax of SUM as an aggregate function is:

SUM ( [DISTINCT/ALL] expression)

The syntax of SUM as an aggregate function is:

SUM ( [DISTINCT/ALL] expression) 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:

  • expression (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 SQL 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.

 

Examples of the SQL 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/2021
2 Susan Johnson 150 150 12/JAN/2021
3 Tom Capper 350 320 06/MAR/2021
4 Mark Holloway 500 410 20/JAN/2021
5 Steven Webber 100 80 09/MAR/2021
6 Julie Armstrong 100 0 12/FEB/2021
7 Michelle Randall 250 23/JAN/2021
8 Andrew Cooper 800 400 04/MAR/2021
9 Robert Pickering 110 100 30/JAN/2021
10 Tanya Hall 150 150 28/JAN/2021

 

Example 1 – Simple SUM

This example shows a simple SUM function on the entire table.

SELECT SUM(fees_paid)
FROM student;

Result:

SUM(FEES_PAID)
1710

This is the total of all fees_paid values in the table. The column header is the function and column, which isn’t easy to work with. Let’s see how we can improve that with a column alias in the next example.

 

Example 2 – Simple SUM with Alias

This example shows a simple SUM function on the entire table with a column alias.

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 3 – 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 4 – 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 5 – 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 6 – 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 7 – 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 8 – 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.

 

What Does SUM OVER or 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 from 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?

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 of how to use SUM with distinct values.

 

Do You Need To Use GROUP BY with SUM?

Not always. You only need to use it if you are selecting fields that are not included in the 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 for more information.

 

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.
  • AVG – This function gets the average value of a supplied expression.
  • COUNT – This function counts the values supplied.

You can find a full list of Oracle SQL functions here. Other function lists are coming soon.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.