GROUP BY with aggregation in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Sum of Sales by Region

Given a sales table with columns Region and Sales, what is the result of this query?

=QUERY(A1:B7, "SELECT A, SUM(B) GROUP BY A", 1)

Data in A1:B7:

Region | Sales
North  | 100
South  | 150
North  | 200
East   | 50
South  | 100
East   | 150

Google Sheets

=QUERY(A1:B7, "SELECT A, SUM(B) GROUP BY A", 1)

A

North | 300
South | 150
East  | 200

B

North | 100
South | 150
East  | 50

C

North | 300
South | 250
East  | 200

D

North | 200
South | 100
East  | 150

💡 Hint

Remember that SUM(B) adds all sales per region.

The query groups rows by region and sums the sales. North has 100 + 200 = 300, South has 150 + 100 = 250, East has 50 + 150 = 200.

🔧 Formula Fix

intermediate

Correct GROUP BY Syntax in QUERY

Which of the following QUERY formulas correctly groups data by Category and counts the number of entries?

A =QUERY(A1:B10, "SELECT A, COUNT(B) GROUP BY A", 1)

B =QUERY(A1:B10, "SELECT A, COUNT(B) WHERE A GROUP BY", 1)

C =QUERY(A1:B10, "SELECT A, COUNT(B) GROUP BY", 1)

D =QUERY(A1:B10, "SELECT COUNT(B) GROUP BY A", 1)

💡 Hint

Check the order of clauses: SELECT, GROUP BY.

Option A correctly uses SELECT A, COUNT(B) GROUP BY A. Other options have syntax errors or wrong clause order.

❓ optimization

advanced

Optimizing Aggregation with Multiple Columns

You want to find the total sales per Region and Product. Which QUERY formula is the most efficient and correct?

A =QUERY(A1:C20, "SELECT A, SUM(C) GROUP BY B", 1)

B =QUERY(A1:C20, "SELECT A, B, SUM(C) GROUP BY A, B", 1)

C =QUERY(A1:C20, "SELECT A, B, SUM(C) GROUP BY A", 1)

D =QUERY(A1:C20, "SELECT SUM(C) GROUP BY A, B", 1)

💡 Hint

When grouping by multiple columns, list all in GROUP BY clause.

Option B correctly groups by both Region (A) and Product (B) and sums sales (C). Others miss grouping columns or select columns not grouped.

🔧 Formula Fix

advanced

Identify the Error in GROUP BY Query

What error will this QUERY formula produce?

=QUERY(A1:B10, "SELECT A, SUM(B)", 1)

Assuming columns A and B have data.

Google Sheets

=QUERY(A1:B10, "SELECT A, SUM(B)", 1)

A Error: SELECT list expression not in GROUP BY clause and contains nonaggregated column 'A'

B No error, returns sum of B for all rows

C Error: Missing WHERE clause

D Error: Invalid column name

💡 Hint

When using aggregation, non-aggregated columns must be in GROUP BY.

The query selects column A and SUM(B) but does not group by A, causing an error.

🧠 Conceptual

expert

Understanding GROUP BY with HAVING Clause

Consider this QUERY formula:

=QUERY(A1:C15, "SELECT A, SUM(C) GROUP BY A HAVING SUM(C) > 100", 1)

What does the HAVING clause do here?

Google Sheets

=QUERY(A1:C15, "SELECT A, SUM(C) GROUP BY A HAVING SUM(C) > 100", 1)

A Filters groups where count of A is greater than 100

B Filters rows where C is greater than 100 before grouping

C Filters rows where A is greater than 100

D Filters groups to only include those where total of C is greater than 100

💡 Hint

HAVING filters after grouping and aggregation.

HAVING applies conditions on aggregated groups. Here it keeps groups with sum of C > 100.

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