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.