How to Use SUM() with GROUP BY: A Guide with 8 Examples

Explore some real-world examples of using SUM() and GROUP BY in SQL, from the most basic to the most sophisticated.

SUM() is a SQL aggregate function that computes the sum of the given values. GROUP BY is a SQL clause that partitions rows into groups and computes a stated aggregate function for each group. Using these two functions together, you can compute total sums for a group of rows.

In this article, we’ll see 8 different examples of how you can combine SUM() and GROUP BY to create many different reports. We’ll talk about the most basic use case and we’ll cover some complex scenarios.

The best way to refresh your SQL skills – including SUM() and GROUP BY – is our interactive SQL Practice Set. It contains 88 interactive exercises that cover different SQL topics. The course is perfect for interview preparation or a review before an SQL exam.

Example 1: Basic Usage of SUM() and GROUP BY in SQL

Let’s look at an example of how the SUM() function works together with GROUP BY:

SELECT 
  country, 
  SUM(quantity) AS total_quantity
FROM orders
GROUP BY country;

The query returns a list of all countries found in the orders table, along with a total sum of the order quantities for each country. The rows in the  orders table are divided into groups (one group for each country) and the database sums the quantity values for each country.

A Detailed Breakdown of Example 1

Let’s analyze this example in detail.

Here is the data in the orders table. This table would be common in an e-commerce system; in addition to the destination country of the goods and the quantity ordered, it includes data on the salesperson who took the order, product SKU, order date, and the customer’s address.

The orders table we will use throughout this article.

The SUM() Function in SQL

The SUM() function is one of SQL’s aggregate functions. Aggregate functions in SQL return a single value for a set of rows. The SUM() function returns the sum of the arguments given to the function. There are other SQL aggregate functions, but we will only focus on SUM() in this article.

When we add SUM() to the query …

SELECT SUM(quantity)
FROM orders;

… we will only get a single value, resulting from summing up all quantity values:

Note that the SUM() function ignores NULL values. They are treated as 0s in the calculation. For more information on the SUM() function, you can read this complete explanation of the SQL SUM() function.

The SUM() function, like other aggregate functions, is typically used with the GROUP BY clause.

The GROUP BY Clause in SQL

The GROUP BY clause is used to compute statistics for a group of rows;  rows are partitioned into groups based on the values of one or more columns. The SELECT statement with GROUP BY returns a single row for each distinct group defined in the GROUP BY clause.

Let’s go back to our example query:

SELECT 
  country, 
  SUM(quantity) AS total_quantity
FROM orders
GROUP BY country;

The rows in the orders table are partitioned into groups based on the value of the country column because of this line: GROUP BY country. It tells the database to place rows with the same country value into one group.

Rows for Mexico are put into one group, rows for New Zealand into another group, and so on. The SUM() function is then applied to quantity values in each group. The sum for the United States is the sum of 10, 12, and 15, resulting in 37. The sum for the United Kingdom is the sum of 18 and 25, resulting in 43, etc.

Here’s the result of our query again. You can see that each row contains the sum of quantity values in the corresponding group.

The GROUP BY clause is especially useful when you want to get summary information from tables with too many rows to go through them one by one. For more information, read a complete explanation of the SQL GROUP BY clause or this article explaining GROUP BY in SQL.

Example 2: Computing 2 SUMs and Grouping By 2 Columns

In the previous example, we saw how to use SUM() and GROUP BY to group a set of data by the column country and get the total quantity for each country separately. You can also group by more than one column with GROUP BY and compute more than one sum in a query.

If we would like to know all the combinations of country and salesperson in the table and get the total of quantities ordered and their amounts for each combination, we need to use SUM() and GROUP BY.

Here’s the query:

SELECT 
  country, 
  salesperson, 
  SUM(quantity) AS qtyTotal,
  SUM(amount) AS amntTotal
FROM orders
GROUP BY country, salesperson;

Based on the table above, this query results in the following data:

We have two expressions that use the SUM() function in the query: one computes the total quantity, and the other computes the total amount. We also group by two columns: country and salesperson.

Internally, the database engine performs the following procedure:

1. It creates a result set from the table specified in the FROM clause, grouping together the rows for each combination of values of the columns specified in the GROUP BY clause. In our example, rows with the same value of country and salesperson are grouped together: there’s one row for the Meghan’s US sales, one row Stephen’s sales in the United States, etc.
2. For each row in the result set created in the previous step, it calculates the sum of each column enclosed in a SUM() In our example, it computes the sum of quantity columns and the sum of amount columns.
3. It returns the result set with the summations.

You can group by more than two columns if you need to. You can read about grouping by multiple columns in our article How to Group by Multiple Columns in SQL.

Example 3: Using  a WHERE Condition with SUM and GROUP BY

You can use a WHERE condition in your query with SUM() and GROUP BY. In this case, the database engine alters the procedure seen above to return the results of the query. It applies the WHERE clause in step 1 of the procedure. Then, the initial result set will be assembled from those rows that meet the WHERE condition.

The columns involved in the WHERE condition can be any of the columns in the table. It does not matter whether or not they are listed in the GROUP BY clause or whether or not they are returned as a result of the query.

Following our example, we could use any of the columns in the orders table, e.g. product_sku or order_date. We’re interested in orders for the product with the SKU 990048006427 that have been made between April 7, 2023 and April 8, 2023.

SELECT 
  country, 
  salesperson, 
  SUM(quantity) AS qtyTotal,
  SUM(amount) AS amntTotal
FROM orders
WHERE 
  product_sku = '990048006427' 
  AND order_date BETWEEN '2023-04-07' AND '2023-04-08'
GROUP BY country, salesperson;

To solve this query, the database engine will first create a temporary result set from the orders table that meets the conditions of the WHERE clause. From that result set, it will take all combinations of the country and salesperson columns. For each combination, it will calculate the sums of quantity and amount.

Note that when we compare the result with Example 2, we only include orders made between April 7, 2023 and April 8, 2023 for the product 990048006427. For example, there are no rows for Meghan’s sales in the United States, as she hasn’t sold this product on these two April days.

Example 4: Using the ORDER BY Clause With SUM and GROUP BY

If we also add an ORDER BY clause to the query we build with SUM() and GROUP BY, then the database engine will have to do a little more work. It adds one more step to the three described above; this step consists of sorting the results according to the criteria specified in the ORDER BY clause.

For example, we could sort the results by the sum of amount, referencing it by its alias amntTotal. We could also specify the full expression SUM(amount):

SELECT 
  country, 
  salesperson, 
  SUM(quantity) AS qtyTotal, 
  SUM(amount) AS amntTotal
FROM orders
GROUP BY country, salesperson
ORDER BY amntTotal DESC;

In this example, we add the DESC clause so that the results are sorted from highest to lowest.

Example 5: Summing Expressions

In addition to being applied to individual columns, the SUM function can also be applied to expressions that return numeric values. Suppose that we have a unit_price column instead of having an amount column. The order amount would result from multiplying quantity by unit_price.

For the SQL SUM GROUP BY to return a sum of the ordered amounts in this case, we will have to apply the SUM() function on the quantity * unit_price expression:

SELECT 
  country, 
  salesperson, 
  SUM(quantity) AS qtyTotal,
  SUM(quantity * unit_price) AS amntTotal
FROM orders
GROUP BY country, salesperson;

The result of the query would be the same as for Example 2:

The procedure performed by the database engine would be a little different. For each group, it would first compute the value of the expression quantity * unit_price and then it would calculate the sums using the computed values.

Example 6: Null Values in the SUM() Function

When using the SUM() function in SQL, NULLs are ignored and are not included in the calculation of the total. If any row in the orders table had NULL values in the quantity or amount columns, they will be ignored by the SUM() function. The exception is if all values are NULL for any of the columns, in which case the SUM() function will also return NULL.

This caveat affects the SUM() and GROUP BY combination. If there are some combinations of the values of the GROUP BY columns where all the totaled values are NULL, then SUM() will also return NULL for that combination of values.

In our sample order table, all rows for country='Mexico' and salesperson='Arthur' have a NULL value in quantity and amount. For that reason, the result of SUM() and GROUP BY returns NULL in the sum of quantity and amount for country = 'Mexico' and salesperson = 'Arthur'. However, neither Arthur nor Mexico are NULL in any of their other combinations.

Have a look:

SELECT 
  country, 
  salesperson, 
  SUM(quantity) AS qtyTotal,
  SUM(amount) AS amntTotal
FROM orders
WHERE country = 'Mexico' AND Salesperson = 'Arthur'
GROUP BY country, salesperson;

If a new row was inserted for country = 'Mexico' and salesperson = 'Arthur' with non-NULL values in quantity and amount, then the rest of the NULL values would be ignored in the total. The SUM() results would include only the values that do not have NULLs in the columns being summed.

Example 7: Converting NULL Values to Zeros

We saw that the SUM() function returns NULL if all the values in the summed column are NULL. If there is only one non-NULL value, the rest of the NULL values are ignored – that is, they are treated as if they were zero.

This can be difficult to explain when doing data storytelling. We want to avoid the word "NULL" appearing in a result box where a number should appear (thus causing confusion to our audience). In such cases, it is convenient to convert the NULL values to zeros. To do this, you can use the COALESCE function. It converts NULL values in a column to a set value – usually zero, although other values can be used.

In our example, we don’t want to run the risk of NULL values in quantity or amount causing “NULL” text to appear in our results. We can enclose these columns in the COALESCE function and send the result of this as a parameter of the SUM() function:

SELECT 
  country, 
  salesperson, 
  SUM(COALESCE(quantity, 0)) AS qtyTotal,
  SUM(COALESCE(amount, 0)) AS amntTotal
FROM orders
GROUP BY country, salesperson;

In this way, all NULLs in the summed columns will appear as zeros.

Note that the row for Arthur’s sales in Mexico now shows 0 instead of NULL.

Example 8: SUM() with Conditionals

Sometimes we want the GROUP BY SUM() combination to only consider data that meets certain conditions.

Continuing with our example, suppose each order has a BIT-type column called delivered that indicates whether the order has been delivered or not. We might want the results of our summations to be divided in two: a total of delivered products and a total of undelivered products. This is solved by using CASE WHEN within the SUM() function to evaluate the content of the delivered column:

SELECT 
  country,
  SUM(CASE 
    WHEN delivered = 1 THEN quantity 
    ELSE 0 END) AS qtyTotalDelivered,
  SUM(CASE 
    WHEN delivered = 0 THEN quantity 
    ELSE 0 END) AS qtyTotalNotDelivered
FROM orders
GROUP BY country;

The CASE WHEN statement is similar to the IF statement in many programming languages; it adds some logic to the flow of our query by evaluating a stated condition. It gives the condition after WHEN. If the condition is met, then the value after THEN is returned. If the condition is not met, the value after ELSE is returned.

CASE 
    WHEN delivered = 1 THEN quantity 
    ELSE 0 END

In this expression, we return the quantity column for delivered products. For other products, we return 0.

Here’s the result of the query:

You can read more about how to use CASE WHEN with SUM and GROUP BY elsewhere in our blog.

Practice Using SUM() with GROUP BY in Your SQL Queries

Throughout this article, we have seen eight examples of how to combine SUM() and GROUP BY in SQL queries. Our orders table – although it has only a few rows – represents real-life situations.

In your work, you will often encounter situations similar to the ones we’ve presented – but your tables will be populated with many more rows. You will then have to make use of all possible variants of SUM() and GROUP BY to prove that the results of your queries are legitimate.

Take advantage of our SQL Practice Set course and SQL Practice track to hone your SQL skills. Remember: The most critical aspect of a data analyst’s job is that the information they provide is 100% reliable.