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

Table of Contents

- Example 1: Basic Usage of SUM() and GROUP BY 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

**table are divided into groups (one group for each country) and the database sums the quantity values for each country.**

`orders`

country | total_quantity |
---|---|

United States | 37 |

United Kingdom | 43 |

Mexico | 12 |

New Zealand | 30 |

### 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_no | order_date | salesperson | product_sku | quantity | amount | address | country |
---|---|---|---|---|---|---|---|

1094802 | 2023-04-03 | Meghan | 990055003464 | 10 | 845.25 | 28 Morningview Lane New York | United States |

1094803 | 2023-04-04 | Stephen | 990048006427 | 12 | 705.5 | 4666 Lawman Avenue Alexandria | United States |

1094804 | 2023-04-05 | Arthur | 770061007219 | 15 | 914.42 | 3445 Queens Lane Lynchburg | United States |

1094805 | 2023-04-06 | Stephen | 990055003464 | 18 | 1012.66 | 93 Iffley Road Brockbridge | United Kingdom |

1094806 | 2023-04-07 | Meghan | 990048006427 | 25 | 2045.72 | 99 Felix Lane Shirley | United Kingdom |

1094807 | 2023-04-08 | Arthur | 770061007219 | Av. Cantera No. 954 Baja California | Mexico | ||

1094808 | 2023-04-05 | Meghan | 990055003464 | Sanchez Marmol 408 Tabasco | Mexico | ||

1094809 | 2023-04-06 | Stephen | 990048006427 | 197 Glencairn Street Dunedin | New Zealand | ||

1094810 | 2023-04-04 | Arthur | 770061007219 | 16 | 842.06 | 139 Petherick Crescent Wellington | New Zealand |

1094811 | 2023-04-09 | Arthur | 990055003464 | Rio Reforma 1730 Jalisco | Mexico | ||

1094812 | 2023-04-08 | Meghan | 990048006427 | 12 | 912.45 | Municipal 114 Veracruz | Mexico |

1094813 | 2023-04-06 | Stephen | 770061007219 | 14 | 799.45 | 105 Queen Elizabeth II Drive Wakapuaka | New 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_no | order_date | salesperson | product_sku | quantity | amount | address | country |
---|---|---|---|---|---|---|---|

1094807 | 2023-04-08 | Arthur | 770061007219 | Av. Cantera No. 954 Baja California | Mexico | ||

1094808 | 2023-04-05 | Meghan | 990055003464 | Sanchez Marmol 408 Tabasco | Mexico | ||

1094811 | 2023-04-09 | Arthur | 990055003464 | Rio Reforma 1730 Jalisco | Mexico | ||

1094812 | 2023-04-08 | Meghan | 990048006427 | 12 | 912.45 | Municipal 114 Veracruz | Mexico |

1094809 | 2023-04-06 | Stephen | 990048006427 | 197 Glencairn Street Dunedin | New Zealand | ||

1094810 | 2023-04-04 | Arthur | 770061007219 | 16 | 842.06 | 139 Petherick Crescent Wellington | New Zealand |

1094813 | 2023-04-06 | Stephen | 770061007219 | 14 | 799.45 | 105 Queen Elizabeth II Drive Wakapuaka | New Zealand |

1094805 | 2023-04-06 | Stephen | 990055003464 | 18 | 1012.66 | 93 Iffley Road Brockbridge | United Kingdom |

1094806 | 2023-04-07 | Meghan | 990048006427 | 25 | 2045.72 | 99 Felix Lane Shirley | United Kingdom |

1094802 | 2023-04-03 | Meghan | 990055003464 | 10 | 845.25 | 28 Morningview Lane New York | United States |

1094803 | 2023-04-04 | Stephen | 990048006427 | 12 | 705.5 | 4666 Lawman Avenue Alexandria | United States |

1094804 | 2023-04-05 | Arthur | 770061007219 | 15 | 914.42 | 3445 Queens Lane Lynchburg | United 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.

country | total_quantity |
---|---|

United States | 37 |

United Kingdom | 43 |

Mexico | 12 |

New Zealand | 30 |

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:

country | salesperson | qtyTotal | amntTotal |
---|---|---|---|

United States | Meghan | 10 | 845.25 |

United States | Stephen | 12 | 705.50 |

United States | Arthur | 15 | 914.42 |

United Kingdom | Stephen | 18 | 1012.65 |

United Kingdom | Meghan | 25 | 2045.72 |

Mexico | Arthur | NULL | NULL |

Mexico | Meghan | 12 | 912.45 |

New Zealand | Stephen | 14 | 799.45 |

New Zealand | Arthur | 16 | 842.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`

.country | salesperson | qtyTotal | amntTotal |
---|---|---|---|

United Kingdom | Meghan | 25 | 2045.72 |

Mexico | Meghan | 12 | 912.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.

country | salesperson | qtyTotal | amntTotal |
---|---|---|---|

United Kingdom | Meghan | 25 | 2045.72 |

United Kingdom | Stephen | 18 | 1012.66 |

United States | Arthur | 15 | 914.42 |

Mexico | Meghan | 12 | 912.45 |

United States | Meghan | 10 | 845.25 |

New Zealand | Arthur | 16 | 842.06 |

New Zealand | Stephen | 14 | 799.45 |

United States | Stephen | 12 | 705.5 |

Mexico | Arthur | NULL | NULL |

## 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:

country | salesperson | qtyTotal | amntTotal |
---|---|---|---|

United States | Meghan | 10 | 845.25 |

United States | Stephen | 12 | 705.50 |

United States | Arthur | 15 | 914.42 |

United Kingdom | Stephen | 18 | 1012.65 |

United Kingdom | Meghan | 25 | 2045.72 |

Mexico | Arthur | NULL | NULL |

Mexico | Meghan | 12 | 912.45 |

New Zealand | Stephen | 14 | 799.45 |

New Zealand | Arthur | 16 | 842.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;

country | salesperson | qtyTotal | amntTotal |
---|---|---|---|

Mexico | Arthur | NULL | NULL |

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.

country | salesperson | qtyTotal | amntTotal |
---|---|---|---|

United States | Meghan | 10 | 845.25 |

United States | Stephen | 12 | 705.50 |

United States | Arthur | 15 | 914.42 |

United Kingdom | Stephen | 18 | 1012.65 |

United Kingdom | Meghan | 25 | 2045.72 |

Mexico | Arthur | 0 | 0.00 |

Mexico | Meghan | 12 | 912.45 |

New Zealand | Stephen | 14 | 799.45 |

New Zealand | Arthur | 16 | 842.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:

country | qtyTotalDelivered | qtyTotalNotDelivered |
---|---|---|

United States | 22 | 15 |

United Kingdom | 18 | 25 |

Mexico | 12 | NULL |

New Zealand | 14 | 16 |

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.