GROUP BY with aggregation in Google Sheets - Step-by-Step Guide

Introduction

Grouping data helps you see summaries like totals or averages for each category. This is useful when you want to combine rows that share the same value and calculate something for each group.

When you want to see total sales for each product category in your sales list.

When you need to find the average score for each student from a list of test results.

When you want to count how many orders each customer made from your order data.

When you want to sum expenses by month from a list of daily expenses.

When you want to group survey responses by answer choice and count how many chose each.

Steps

Step 1: Click

- an empty cell where you want the grouped result

The cell is selected and ready for formula input

Step 2: Type

- the selected cell

You start entering the formula

💡 Start with =QUERY to use grouping and aggregation

Step 3: Enter

- the formula bar

The formula is ready to run

💡 Use syntax like =QUERY(A1:C10, "select A, sum(B) group by A", 1)

Step 4: Press

- Enter key

The grouped and aggregated data appears in the sheet

Step 5: Check

- the output cells

You see each unique value from the group column with the aggregated result next to it

Before vs After

Before

A list of sales with columns: Product, Quantity, Price. Multiple rows have the same Product name.

After

A summary table showing each Product once with the total Quantity sold for that product.

Settings Reference

Query range

📍 First argument of QUERY function

Defines the data to group and aggregate

Default: None

Query string

📍 Second argument of QUERY function

Defines how to group and what to calculate

Default: None

Headers

📍 Third argument of QUERY function

Specifies how many header rows the data has

Default: 1

Common Mistakes

Forgetting to put the query string in quotes

Google Sheets will show an error because the formula syntax is incorrect.

Always put the query string inside double quotes, for example: "select A, sum(B) group by A"

Not matching the group by columns with the select columns

The QUERY function requires that all columns in select that are not aggregated must be in group by.

Include all non-aggregated columns in the group by clause exactly as in the select clause.

Using aggregation functions on text columns

Aggregation functions like sum or avg only work on numbers, not text.

Apply aggregation only on numeric columns, and group by text columns.

Summary

Use the QUERY function to group data by one or more columns and calculate totals, averages, or counts.

The query string must include select, aggregation functions, and group by clauses correctly.

Always put the query string in double quotes and match group by columns with selected non-aggregated columns.

Practice

1. What does the GROUP BY clause do in a Google Sheets QUERY function?

easy

A. It deletes duplicate rows from the data.

B. It sorts the rows in ascending order.

C. It filters rows based on a condition.

D. It groups rows that have the same value in specified columns.

Solution

1. Step 1: Understand the purpose of GROUP BY

   The GROUP BY clause collects rows that share the same value in one or more columns into groups.

2. Step 2: Differentiate from other clauses

   Unlike sorting or filtering, GROUP BY organizes data into groups for aggregation.

3. Final Answer:

   It groups rows that have the same value in specified columns. -> Option D

4. Quick Check:

   GROUP BY = groups rows by column values [OK]

Hint: GROUP BY groups rows with same column values [OK]

Common Mistakes:

• Confusing GROUP BY with sorting
• Thinking GROUP BY filters rows
• Assuming GROUP BY removes duplicates

2. Which of the following is the correct syntax to sum values in column B grouped by column A using QUERY in Google Sheets?

easy

A. =QUERY(A1:B10, 'SELECT A, SUM(B)')

B. =QUERY(A1:B10, 'GROUP BY A, SUM(B)')

C. =QUERY(A1:B10, 'SELECT A, SUM(B) GROUP BY A')

D. =QUERY(A1:B10, 'SELECT SUM(B) GROUP BY A')

Solution

1. Step 1: Identify correct QUERY syntax

   The QUERY function requires SELECT clause listing columns and aggregation, followed by GROUP BY clause.

2. Step 2: Check each option

   =QUERY(A1:B10, 'SELECT A, SUM(B) GROUP BY A') correctly uses 'SELECT A, SUM(B) GROUP BY A'. =QUERY(A1:B10, 'SELECT A, SUM(B)') misses GROUP BY. =QUERY(A1:B10, 'GROUP BY A, SUM(B)') has wrong clause order. =QUERY(A1:B10, 'SELECT SUM(B) GROUP BY A') misses selecting column A.

3. Final Answer:

   =QUERY(A1:B10, 'SELECT A, SUM(B) GROUP BY A') -> Option C

4. Quick Check:

   Correct QUERY syntax = =QUERY(A1:B10, 'SELECT A, SUM(B) GROUP BY A') [OK]

Hint: Use SELECT columns, aggregation, then GROUP BY columns [OK]

Common Mistakes:

• Omitting GROUP BY clause
• Wrong clause order in QUERY
• Not selecting grouped column

3. Given the data in A1:B5:
A | B
--|--
Cat | 2
Dog | 3
Cat | 4
Dog | 1
Bird | 5
What is the output of this QUERY?
=QUERY(A1:B5, 'SELECT A, SUM(B) GROUP BY A ORDER BY SUM(B) DESC')

medium

A. [[Cat, 6], [Bird, 5], [Dog, 4]]

B. [[Bird, 5], [Cat, 6], [Dog, 4]]

C. [[Dog, 4], [Cat, 6], [Bird, 5]]

D. [[Cat, 6], [Dog, 4], [Bird, 5]]

Solution

1. Step 1: Calculate SUM(B) for each group

   Cat: 2 + 4 = 6, Dog: 3 + 1 = 4, Bird: 5

2. Step 2: Order groups by SUM(B) descending

   Order is Cat (6), Bird (5), Dog (4)

3. Final Answer:

   [[Cat, 6], [Bird, 5], [Dog, 4]] -> Option A

4. Quick Check:

   SUM and ORDER BY correct = [[Cat, 6], [Bird, 5], [Dog, 4]] [OK]

Hint: Sum groups, then order by sum descending [OK]

Common Mistakes:

• Mixing order of groups
• Adding values incorrectly
• Ignoring ORDER BY clause

4. You wrote this QUERY:
=QUERY(A1:C10, 'SELECT A, C, COUNT(B) GROUP BY A')
But it returns an error. What is the likely cause?

medium

A. You must include all non-aggregated columns in GROUP BY.

B. Column C is not included in SELECT or GROUP BY but is in data range.

C. Column B contains empty cells causing COUNT to fail.

D. COUNT function cannot be used in QUERY.

Solution

1. Step 1: Understand GROUP BY rules in QUERY

   All columns in SELECT that are not aggregated must appear in GROUP BY clause.

2. Step 2: Check columns in SELECT and GROUP BY

   A is non-aggregated and included in GROUP BY, COUNT(B) is aggregated, but C is non-aggregated and missing from GROUP BY.

3. Final Answer:

   You must include all non-aggregated columns in GROUP BY. -> Option A

4. Quick Check:

   Non-aggregated columns must be in GROUP BY [OK]

Hint: Group all non-aggregated selected columns [OK]

Common Mistakes:

• Ignoring GROUP BY requirements
• Assuming COUNT fails on empty cells
• Thinking COUNT is unsupported

5. You have sales data with columns: Region, Salesperson, and Amount. You want to find the total sales per region but only include regions where total sales exceed 1000. Which QUERY formula achieves this?

hard

A. =QUERY(A1:C100, 'SELECT Region, SUM(Amount) GROUP BY Region WHERE SUM(Amount) > 1000')

B. =QUERY(A1:C100, 'SELECT Region, SUM(Amount) GROUP BY Region HAVING SUM(Amount) > 1000')

C. =QUERY(A1:C100, 'SELECT Region, SUM(Amount) WHERE SUM(Amount) > 1000 GROUP BY Region')

D. =QUERY(A1:C100, 'SELECT Region, SUM(Amount) HAVING SUM(Amount) > 1000 GROUP BY Region')

Solution

1. Step 1: Use GROUP BY to sum sales per region

   The query must group rows by Region and sum Amount.

2. Step 2: Filter groups with HAVING clause

   HAVING filters groups after aggregation; use HAVING SUM(Amount) > 1000.

3. Step 3: Check clause order

   Correct order is SELECT, GROUP BY, HAVING. WHERE cannot filter aggregated results.

4. Final Answer:

   =QUERY(A1:C100, 'SELECT Region, SUM(Amount) GROUP BY Region HAVING SUM(Amount) > 1000') -> Option B

5. Quick Check:

   Use HAVING for aggregated filters [OK]

Hint: Use HAVING to filter aggregated groups [OK]

Common Mistakes:

• Using WHERE to filter aggregated results
• Wrong clause order in QUERY
• Omitting HAVING clause