30th May 2023 13 minutes read How to Use SUM() with GROUP BY: A Guide with 8 Examples Gustavo du Mortier sql learn sql group by aggregate functions Table of Contents Example 1: Basic Usage of SUM() and GROUP BY in SQL A Detailed Breakdown of Example 1 The SUM() Function in SQL The GROUP BY Clause in SQL Example 2: Computing 2 SUMs and Grouping By 2 Columns Example 3: Using a WHERE Condition with SUM and GROUP BY Example 4: Using the ORDER BY Clause With SUM and GROUP BY Example 5: Summing Expressions Example 6: Null Values in the SUM() Function Example 7: Converting NULL Values to Zeros Example 8: SUM() with Conditionals Practice Using SUM() with GROUP BY in Your SQL Queries 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. countrytotal_quantity United States37 United Kingdom43 Mexico12 New Zealand30 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. order_noorder_datesalespersonproduct_skuquantityamountaddresscountry 10948022023-04-03Meghan99005500346410845.2528 Morningview Lane New YorkUnited States 10948032023-04-04Stephen99004800642712705.54666 Lawman Avenue AlexandriaUnited States 10948042023-04-05Arthur77006100721915914.423445 Queens Lane LynchburgUnited States 10948052023-04-06Stephen990055003464181012.6693 Iffley Road BrockbridgeUnited Kingdom 10948062023-04-07Meghan990048006427252045.7299 Felix Lane ShirleyUnited Kingdom 10948072023-04-08Arthur770061007219Av. Cantera No. 954 Baja CaliforniaMexico 10948082023-04-05Meghan990055003464Sanchez Marmol 408 TabascoMexico 10948092023-04-06Stephen990048006427197 Glencairn Street DunedinNew Zealand 10948102023-04-04Arthur77006100721916842.06139 Petherick Crescent WellingtonNew Zealand 10948112023-04-09Arthur990055003464Rio Reforma 1730 JaliscoMexico 10948122023-04-08Meghan99004800642712912.45Municipal 114 VeracruzMexico 10948132023-04-06Stephen77006100721914799.45105 Queen Elizabeth II Drive WakapuakaNew Zealand 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: SUM(quantity) 122 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. order_noorder_datesalespersonproduct_skuquantityamountaddresscountry 10948072023-04-08Arthur770061007219Av. Cantera No. 954 Baja CaliforniaMexico 10948082023-04-05Meghan990055003464Sanchez Marmol 408 TabascoMexico 10948112023-04-09Arthur990055003464Rio Reforma 1730 JaliscoMexico 10948122023-04-08Meghan99004800642712912.45Municipal 114 VeracruzMexico 10948092023-04-06Stephen990048006427197 Glencairn Street DunedinNew Zealand 10948102023-04-04Arthur77006100721916842.06139 Petherick Crescent WellingtonNew Zealand 10948132023-04-06Stephen77006100721914799.45105 Queen Elizabeth II Drive WakapuakaNew Zealand 10948052023-04-06Stephen990055003464181012.6693 Iffley Road BrockbridgeUnited Kingdom 10948062023-04-07Meghan990048006427252045.7299 Felix Lane ShirleyUnited Kingdom 10948022023-04-03Meghan99005500346410845.2528 Morningview Lane New YorkUnited States 10948032023-04-04Stephen99004800642712705.54666 Lawman Avenue AlexandriaUnited States 10948042023-04-05Arthur77006100721915914.423445 Queens Lane LynchburgUnited States 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. countrytotal_quantity United States37 United Kingdom43 Mexico12 New Zealand30 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: countrysalespersonqtyTotalamntTotal United StatesMeghan10845.25 United StatesStephen12705.50 United StatesArthur15914.42 United KingdomStephen181012.65 United KingdomMeghan252045.72 MexicoArthurNULLNULL MexicoMeghan12912.45 New ZealandStephen14799.45 New ZealandArthur16842.06 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: 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. 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. 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. countrysalespersonqtyTotalamntTotal United KingdomMeghan252045.72 MexicoMeghan12912.45 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. countrysalespersonqtyTotalamntTotal United KingdomMeghan252045.72 United KingdomStephen181012.66 United StatesArthur15914.42 MexicoMeghan12912.45 United StatesMeghan10845.25 New ZealandArthur16842.06 New ZealandStephen14799.45 United StatesStephen12705.5 MexicoArthurNULLNULL 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: countrysalespersonqtyTotalamntTotal United StatesMeghan10845.25 United StatesStephen12705.50 United StatesArthur15914.42 United KingdomStephen181012.65 United KingdomMeghan252045.72 MexicoArthurNULLNULL MexicoMeghan12912.45 New ZealandStephen14799.45 New ZealandArthur16842.06 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; countrysalespersonqtyTotalamntTotal MexicoArthurNULLNULL 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. countrysalespersonqtyTotalamntTotal United StatesMeghan10845.25 United StatesStephen12705.50 United StatesArthur15914.42 United KingdomStephen181012.65 United KingdomMeghan252045.72 MexicoArthur00.00 MexicoMeghan12912.45 New ZealandStephen14799.45 New ZealandArthur16842.06 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: countryqtyTotalDeliveredqtyTotalNotDelivered United States2215 United Kingdom1825 Mexico12NULL New Zealand1416 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. Tags: sql learn sql group by aggregate functions