HomeTutorialsSQL

# Understanding the SQL SUM() Function and Its Use Cases

Discover the power of the SQL SUM() function for data aggregation. Learn how to implement rolling sums, cumulative sums, and sum multiple columns effectively.
Jul 2024  · 8 min read

The SQL `SUM()` function is a useful aggregate function for calculating totals of numerical columns. The `SUM()` function is important in data analysis because it summarizes key metrics of particular numerical columns. Due to its straightforward applications, the `SUM()` function helps data analysts derive meaningful insights from large datasets. Additionally, this versatile function is supported across different databases, including SQL Server, PostgreSQL, MySQL, and Oracle.

If you are an aspiring or seasoned data analyst ready to advance your analytic skills, I highly recommend taking DataCamp’s Introduction to SQL and Intermediate SQL courses to improve your data analysis skills. The SQL Basics Cheat Sheet will also be a good reference as you continue your journey.

## How to Use the SUM() Function in SQL

The SQL `SUM()` function is used to aggregate the totals of a particular column in a dataset. Consider the table `orders` with the following structure.

Example table to aggregate column totals using SUM() function. Image by Author.

The query below will return the total quantities from the `quantity` column, aliased as `total_quantity`, and the revenue calculated from the sum of the product of `quantity` and `price`, aliased as `total_revenue`.

Notice how, in the case of `total_revenue`, the `SUM()` function is being used with an expression to perform an additional calculation on the column before getting the totals. Also, notice how the `SUM()` function ignores the `NULL` values when calculating the totals.

``````-- Select the total quantity and the total revenue
-- Calculate the total quantity of all orders and alias 'total_quantity'.
-- Calculate the total revenue and assigns it an alias 'total_revenue'.
SELECT
SUM(quantity) AS total_quantity,
SUM(quantity * price) AS total_revenue
FROM
orders;``````

Example output table of SUM() function using an alias and expression. Image by Author.

## Use Cases of the SUM() Function in SQL

The `SUM()` function in SQL can also be used to perform advanced calculations. Let's look at some examples.

### Calculate a cumulative sum in SQL

A cumulative sum refers to the running total of a numeric column. Calculating cumulative sums is important as it helps track the totals over different periods for important metrics such as revenue or sales. The query below shows how to use the `SUM()` function to calculate the cumulative totals of the quantity column.

``````-- Calculate cumulative sums of the 'quantity' column for each order_date
SELECT
order_date,
quantity,
-- Calculate the cumulative sum of 'quantity' ordered by 'order_date'
SUM(quantity) OVER (ORDER BY order_date) AS cumulative_quantity
FROM
orders;``````

Example table output using SQL SUM() function to calculate cumulative sums. Image by Author.

I recommend taking DataCamp’s Data Manipulation in SQL course, which details the use of window functions in aggregation for techniques such as calculating running totals. The SQL Window Functions Cheat Sheet is also an appropriate guide to quickly reference the different window functions and their applications.

### Sum multiple columns in SQL

The `SUM()` function can also be used to calculate the totals of multiple columns. This approach aggregates data from multiple columns into one. Consider the new `orders` table below, with the columns `opening_quantity` and `received_quantity`.

Example table to sum multiple columns using SQL SUM() function. Image by Author.

The example below shows how to use the `SUM()` function to calculate the combined totals of the `opening_quantity` and `received_quantity` columns. Here the `+` operator is used to perform arithmetic addition between two numerical values.

``````-- Calculate the total quantity of opening_quantity and received_quantity columns
-- Sum the total values in one column
SELECT
FROM
orders;``````

Example table output using SQL SUM() function for summing multiple columns. Image by Author.

### Calculate a rolling sum in SQL

The rolling sum allows the totals to be calculated based on a specified number of preceding rows. The `SUM()` function in SQL calculates the rolling sum for important metrics such as 7-day total sales. The query below gives an example of using the `SUM()` function to calculate the rolling sum of `quantity` over the last 3 rows ordered by `order_date`.

``````-- Calculate rolling sums of the 'quantity' column for the past 3 orders for each order_date
SELECT
order_date,
quantity,
-- Calculate the rolling sum of 'quantity' over the last 3 rows ordered by 'order_date'
SUM(quantity) OVER (
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_quantity
FROM
orders;``````

Example table output of calculating rolling sum in SQL using the SUM() function. Image by Author.

### Sum DISTINCT values in SQL

You can sum the values of a particular column using the `DISTINCT` keyword to calculate the totals of only the unique values. The example below shows the `DISTINCT` keyword used to sum the unique values in the `quantity` and `price` columns.

``````-- Sum the distinct values of the quantity and price columns
SELECT
SUM(DISTINCT quantity) AS total_distinct_quantity,
SUM(DISTINCT price) AS total_distinct_price
FROM
orders;``````

## Sum and Other SQL Aggregate Functions

As we have seen, the SQL `SUM()` function is a powerful tool and more versatile than it seems. Still, it's just one of many aggregate functions in SQL. Here is a table that summarizes the most common SQL aggregate functions, their descriptions, and examples:

Function Description Example
SUM() Calculates the total sum of a numeric column. Total quantity and revenue.
AVG() Calculates the average value of a numeric column. Average price of products.
MIN() Returns the smallest value in a column. Minimum quantity of orders.
MAX() Returns the largest value in a column. Maximum price of products.
COUNT() Returns the number of rows that match a specified condition. Total number of orders.

## Conclusion

Learning the `SUM()` function in SQL is important to understand its different applications in data analysis. The `SUM()` function is supported across the major databases and has a similar implementation with different clauses and window functions. I encourage you to practice the applications of the `SUM()` function with different datasets to sharpen your SQL skills.

If you want to advance your SQL skills for business analytics, I encourage you to check DataCamp’s SQL Fundamentals skill track to understand the on-demand skills. Similarly, I encourage you to check out our Associate Data Analyst in SQL career track to help you learn and master the necessary SQL skills to advance your career. Finally, I highly recommend obtaining DataCamp’s SQL Associate Certification to demonstrate the mastery of SQL skills and help you stand out among other data analysts.

Author
Allan Ouko
I create articles that simplify data science and analytics, making them easy to understand and accessible.

### What is the SQL SUM() function?.css-18x2vi3{-webkit-flex-shrink:0;-ms-flex-negative:0;flex-shrink:0;height:18px;padding-top:6px;-webkit-transform:rotate(0.5turn) translate(21%, -10%);-moz-transform:rotate(0.5turn) translate(21%, -10%);-ms-transform:rotate(0.5turn) translate(21%, -10%);transform:rotate(0.5turn) translate(21%, -10%);-webkit-transition:-webkit-transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);transition:transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);width:18px;}

The `SUM()` function calculates the total values of a numeric column across a table/dataset.

### What is the difference between SUM() and COUNT() functions?.css-167dpqb{-webkit-flex-shrink:0;-ms-flex-negative:0;flex-shrink:0;height:18px;padding-top:6px;-webkit-transform:none;-moz-transform:none;-ms-transform:none;transform:none;-webkit-transition:-webkit-transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);transition:transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);width:18px;}

The `SUM()` function calculates the total values in a column. The `COUNT()` function returns the total number of rows based on a condition.

### How does the SUM() function handle NULL values?

When calculating the totals, the `SUM()` function ignores the `NULL` values in a column.

### Can one use the DISTINCT keyword with the SUM() function?

The `DISTINCT` keyword is used with the `SUM()` function to calculate the totals of unique values in a column.

### How can you use the SUM() function with window functions?

The `SUM()` function can be used as a window function with the `OVER` clause to compute cumulative sums and other calculations across partitions of the data.

### What other functions in SQL are similar to SUM()?

Other similar aggregate functions in SQL include `AVG()` for averages, `COUNT()` for row counts, `MIN()` for the smallest value, `MAX()` for the largest, `COUNT(DISTINCT)` for unique counts, `GROUP_CONCAT()` for string concatenation, and `STDDEV()` or `VARIANCE()` for statistical calculations. These functions help efficiently summarize and analyze data.

Topics

Learn SQL with DataCamp

Course

### .css-1531qan{-webkit-text-decoration:none;text-decoration:none;color:inherit;}Introduction to SQL

2 hr
741.3K
Learn how to create and query relational databases using SQL in just two hours.
See Details
Start Course

Course

### Joining Data in SQL

4 hr
147.7K
Level up your SQL knowledge and learn to join tables together, apply relational set theory, and work with subqueries.

Course

### Data Manipulation in SQL

4 hr
226.2K
Master the complex SQL queries necessary to answer a wide variety of data science questions and prepare robust data sets for analysis in PostgreSQL.
See More
Related

tutorial

### Aggregate Functions in SQL

Learn how to use aggregate functions for summarizing results and gaining useful insights about data in SQL.

Sayak Paul

9 min

tutorial

### COUNT() SQL FUNCTION

COUNT() lets you count the number of rows that match certain conditions. Learn how to use it in this tutorial.

Travis Tang

3 min

tutorial

### How to Use the SUMX Power BI Functions

Explore the SUMX function in Power BI, its syntax, how it works, and best practices to keep in mind.

Joleen Bothma

8 min

tutorial

### How to Use GROUP BY and HAVING in SQL

An intuitive guide for discovering the two most popular SQL commands to aggregate rows of your dataset

Eugenia Anello

6 min

tutorial

### SQL: Reporting and Analysis

Master SQL for Data Reporting & daily data analysis by learning how to select, filter & sort data, customize output, & how you can report aggregated data from a database!

Hafsa Jabeen

37 min

tutorial

### Understanding the LAG() Function in SQL: A Comprehensive Guide

Explore how the LAG() function allows you to access previous rows in your dataset, enabling time series analysis and comparisons of sequential observations.

Islam Salahuddin

11 min

See MoreSee More