GROUP BY with aggregation in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the GROUP BY clause do in a query?

GROUP BY groups rows that have the same values in specified columns into summary rows, like grouping all sales by each product.

beginner

Which function would you use to find the total sum of a column after grouping?

You use the SUM() function to add up values in a column for each group.

intermediate

How do you write a query to count the number of entries per category in Google Sheets QUERY function?

Use: SELECT Category, COUNT(Column) GROUP BY Category

beginner

Why do you need to use GROUP BY when using aggregation functions?

Because aggregation functions like SUM or COUNT summarize data per group, GROUP BY tells the query how to group rows before summarizing.

intermediate

In Google Sheets QUERY, what happens if you use an aggregation function without GROUP BY?

The aggregation function will calculate over the entire data set, returning a single summary value instead of grouped results.

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

A Filters rows based on a condition

B Deletes duplicate rows

C Sorts rows alphabetically

D Groups rows with the same values in specified columns

GROUP BY groups rows that share the same values in specified columns to prepare for aggregation.

Which aggregation function counts the number of rows in each group?

A COUNT()

B MAX()

C SUM()

D MIN()

COUNT() counts the number of rows or non-empty values in each group.

What will this QUERY return? SELECT Category, SUM(Sales) GROUP BY Category

A All sales rows without grouping

B Total sales for each category

C Sum of all sales without grouping

D Number of categories

It sums sales values for each category separately.

If you forget to add GROUP BY when using SUM(), what happens?

A SUM() returns zero

B You get an error

C SUM() sums all rows ignoring groups

D SUM() counts rows instead

Without GROUP BY, SUM() calculates the total over all rows.

Which of these is a valid Google Sheets QUERY with grouping?

A SELECT Name, COUNT(Age) GROUP BY Name

B SELECT Name, COUNT(Age)

C SELECT COUNT(Age) GROUP BY Name

D SELECT Name, Age

When using COUNT() with another column, you must GROUP BY that column.

Explain how GROUP BY works with aggregation functions in Google Sheets QUERY.

Think about how you would summarize sales by product category.

Describe a real-life example where you would use GROUP BY with SUM in Google Sheets.

Imagine you want to know total sales per product.

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