Cours
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_revenueFROM 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_dateSELECT order_date, quantity, -- Calculate the cumulative sum of 'quantity' ordered by 'order_date' SUM(quantity) OVER (ORDER BY order_date) AS cumulative_quantityFROM 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 columnSELECT SUM(opening_quantity + received_quantity) AS total_quantityFROM 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_dateSELECT 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_quantityFROM 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 columnsSELECT SUM(DISTINCT quantity) AS total_distinct_quantity, SUM(DISTINCT price) AS total_distinct_priceFROM 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.
Frequently Asked Questions
What is the SQL SUM() function?
The SUM()
function calculates the total values of a numeric column across a table/dataset.
What is the difference between SUM() and COUNT() functions?
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.