GROUP BY with aggregation in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a summary report showing total sales and average sales per region.

📊 Data: You have a sales data table with columns: Date, Region, Salesperson, and Sales Amount.

🎯 Deliverable: Create a summary table that groups data by Region and shows Total Sales and Average Sales for each region.

Sample Data

Date	Region	Salesperson	Sales Amount
2024-06-01	North	Alice	500
2024-06-02	South	Bob	300
2024-06-03	North	Charlie	700
2024-06-04	East	Diana	400
2024-06-05	South	Eva	600
2024-06-06	East	Frank	200
2024-06-07	North	Grace	300
2024-06-08	West	Hank	450
2024-06-09	West	Ivy	550
2024-06-10	South	Jack	400

1

Step 1: Select a blank area in your sheet to create the summary table.

No formula needed for this step.

Expected Result

You have space ready to build the summary.

2

Step 2: Enter the formula to group data by Region and calculate total and average sales.

=QUERY(A1:D11, "select B, sum(D), avg(D) group by B label sum(D) 'Total Sales', avg(D) 'Average Sales'", 1)

Expected Result

A table showing each Region with its Total Sales and Average Sales.

3

Step 3: Format the Total Sales and Average Sales columns as currency for better readability.

Select the Total Sales and Average Sales columns, then Format > Number > Currency.

Expected Result

Sales amounts appear with currency symbols and two decimal places.

Final Result

Region   | Total Sales | Average Sales
-------------------------------------
East     | $600.00     | $300.00
North    | $1,500.00   | $500.00
South    | $1,300.00   | $433.33
West     | $1,000.00   | $500.00

✓North region has the highest total sales of $1,500.

✓South region has an average sale of about $433.33, which is lower than North and West.

✓East region has the lowest total sales but a moderate average sale.

Bonus Challenge

Add a column to the summary that shows the number of sales transactions per region.

Show Hint

Modify the QUERY formula to include count(D) and label it as 'Number of Sales'.

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