GROUP BY with aggregation in Google Sheets - Dashboard Guide

Dashboard Mode - GROUP BY with aggregation

Goal

Find total sales for each product category to see which category sells the most.

Sample Data

Order ID	Product	Category	Sales
1001	Notebook	Stationery	15
1002	Pen	Stationery	10
1003	Chair	Furniture	120
1004	Desk	Furniture	250
1005	Stapler	Stationery	8
1006	Lamp	Furniture	75
1007	Marker	Stationery	12

Dashboard Components

• Summary Table: Shows total sales per category.
  =QUERY(A1:D8, "select C, sum(D) group by C", 1)
  This groups sales by category and sums them.
• Top Category KPI: Shows the category with highest total sales.
  =INDEX(QUERY(A1:D8, "select C, sum(D) group by C order by sum(D) desc limit 1", 1), 2, 1)
  This finds the category with max sales.
• Total Sales KPI: Shows total sales across all categories.
  =SUM(D2:D8)

Dashboard Layout

+----------------------+------------------+
| Total Sales (KPI)    | Top Category (KPI)|
+----------------------+------------------+
|          Summary Table (Grouped Sales)       |
+----------------------------------------------+

Interactivity

Add a filter dropdown for Category. When you select a category, the Summary Table and KPIs update to show data only for that category.

Self Check

If you filter to show only 'Furniture' category, what is the total sales shown in the Total Sales KPI?

Answer: 445 (120 + 250 + 75)

Key Result

Dashboard shows total sales grouped by product category with key sales metrics.

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