SQL SUM() Function Explained

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:

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.