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.