Why QUERY is Google Sheets' most powerful function - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants a clear summary of sales data filtered by region and product category, showing total sales and average price.

📊 Data: You have a sales dataset with columns: Date, Region, Product Category, Product Name, Units Sold, Unit Price, and Total Sales.

🎯 Deliverable: Create a summary table using the QUERY function that shows Region, Product Category, total Units Sold, average Unit Price, and total Sales, filtered to only include regions with sales above 1000 units.

Sample Data

Date	Region	Product Category	Product Name	Units Sold	Unit Price	Total Sales
2024-01-05	East	Electronics	Headphones	150	50	7500
2024-01-07	West	Electronics	Speakers	200	80	16000
2024-01-10	East	Furniture	Desk	100	120	12000
2024-01-12	North	Electronics	Monitor	300	150	45000
2024-01-15	South	Furniture	Chair	80	75	6000
2024-01-18	West	Furniture	Table	120	200	24000
2024-01-20	North	Furniture	Shelf	90	100	9000
2024-01-22	East	Electronics	Camera	130	300	39000
2024-01-25	South	Electronics	Smartphone	250	400	100000
2024-01-28	West	Electronics	Tablet	180	350	63000

1

Step 1: Select the entire data range including headers (A1:G11).

No formula needed.

Expected Result

Data range selected for QUERY function.

2

Step 2: Enter the QUERY formula to summarize total Units Sold, average Unit Price, and total Sales by Region and Product Category.

=QUERY(A1:G11, "select B, C, sum(E), avg(F), sum(G) group by B, C", 1)

Expected Result

Table showing Region, Product Category, sum of Units Sold, average Unit Price, and sum of Total Sales.

3

Step 3: Modify the QUERY formula to include only regions where total Units Sold is greater than 1000.

=QUERY(A1:G11, "select B, C, sum(E), avg(F), sum(G) group by B, C having sum(E) > 1000", 1)

Expected Result

Filtered summary table excluding regions with total Units Sold 1000 or less.

4

Step 4: Format the summary table headers for clarity: Region, Product Category, Total Units Sold, Average Unit Price, Total Sales.

Manually rename headers in the output or use QUERY label clause.

Expected Result

Clear headers on the summary table for easy understanding.

5

Step 5: Use the QUERY label clause to rename columns in the formula for better readability.

=QUERY(A1:G11, "select B, C, sum(E), avg(F), sum(G) group by B, C having sum(E) > 1000 label sum(E) 'Total Units Sold', avg(F) 'Average Unit Price', sum(G) 'Total Sales'", 1)

Expected Result

Summary table with friendly column names.

Final Result

Region    | Product Category | Total Units Sold | Average Unit Price | Total Sales
----------|------------------|------------------|--------------------|------------
East      | Electronics      | 280              | 175                | 46500
West      | Electronics      | 580              | 170                | 79000
West      | Furniture        | 120              | 200                | 24000
North     | Electronics      | 300              | 150                | 45000
South     | Electronics      | 250              | 400                | 100000

✓East and West regions have strong sales in Electronics and Furniture.

✓South region has high sales in Electronics but lower in Furniture.

✓North region has balanced sales but lower total units compared to West.

✓Regions with total units sold above 1000 are included, filtering out smaller sales.

Bonus Challenge

Create a dynamic QUERY formula that updates the summary automatically when new data rows are added below the current data.

Show Hint

Use an open-ended range like A1:G or use named ranges to include new data automatically.

Practice

1. What is the main advantage of using the QUERY function in Google Sheets?

easy

A. It lets you filter and organize data without changing the original table.

B. It automatically formats your spreadsheet with colors.

C. It creates charts from your data instantly.

D. It deletes duplicate rows from your data.

Solution

1. Step 1: Understand QUERY's purpose

   The QUERY function is designed to filter, sort, and summarize data without altering the original dataset.

2. Step 2: Compare options to QUERY's features

   Options B, C, and D describe other functions or manual actions, not QUERY's main use.

3. Final Answer:

   It lets you filter and organize data without changing the original table. -> Option A

4. Quick Check:

   QUERY filters and organizes data [OK]

Hint: QUERY filters data without changing original table [OK]

Common Mistakes:

• Thinking QUERY formats cells automatically
• Confusing QUERY with chart creation
• Assuming QUERY deletes data

2. Which of the following is the correct syntax to select columns A and B from a range using QUERY?

easy

A. =QUERY(A1:C10, "SELECT 1, 2")

B. =QUERY(A1:C10, "SELECT Col1, Col2")

C. =QUERY(A1:C10, "SELECT A, B")

D. =QUERY(A1:C10, "SELECT A:B")

Solution

1. Step 1: Recall QUERY column naming

   In QUERY, columns are referenced by Col1, Col2, etc. in the SELECT clause.

2. Step 2: Check each option's syntax

   =QUERY(A1:C10, "SELECT Col1, Col2") uses correct syntax. =QUERY(A1:C10, "SELECT 1, 2") uses invalid numbers, =QUERY(A1:C10, "SELECT A, B") uses invalid letters, =QUERY(A1:C10, "SELECT A:B") uses invalid range format.

3. Final Answer:

   =QUERY(A1:C10, "SELECT Col1, Col2") -> Option B

4. Quick Check:

   Use Col1, Col2 in SELECT [OK]

Hint: Use Col1, Col2 in QUERY SELECT clause [OK]

Common Mistakes:

• Using numbers instead of Col1, Col2 for columns
• Trying to select a range like A:B inside SELECT
• Using column letters A, B instead of Col1, Col2

3. Given the data in A1:B5:

A    B
1    10
2    20
3    30
4    40
5    50

What will =QUERY(A1:B5, "SELECT Col1 WHERE Col2 > 25") return?

medium

A. [1, 2]

B. [30, 40, 50]

C. [3, 4, 5]

D. [10, 20, 30]

Solution

1. Step 1: Understand the QUERY condition

   The query selects column A (Col1) where values in column B (Col2) are greater than 25.

2. Step 2: Identify rows where B > 25

   Rows with B=30, 40, 50 correspond to A=3, 4, 5.

3. Final Answer:

   [3, 4, 5] -> Option C

4. Quick Check:

   Filter B > 25, select A [OK]

Hint: Filter rows by condition, then select column [OK]

Common Mistakes:

• Selecting values from column B instead of A
• Including rows where B ≤ 25
• Confusing column letters with values

4. The formula =QUERY(A1:C10, "SELECT Col1, Col2, SUM(Col3) GROUP BY Col1") returns an error. What is the likely cause?

medium

A. All selected columns must be in GROUP BY or aggregated

B. SUM(Col3) cannot be used without aliasing

C. Column C must be included in SELECT when grouping

D. Missing a WHERE clause before GROUP BY

Solution

1. Step 1: Understand GROUP BY rules in QUERY

   When using GROUP BY, all selected columns must be either grouped or aggregated.

2. Step 2: Analyze the formula's SELECT clause

   Col1 is grouped, SUM(Col3) is aggregated, but Col2 is neither, causing the error.

3. Final Answer:

   All selected columns must be in GROUP BY or aggregated -> Option A

4. Quick Check:

   GROUP BY requires all columns grouped or aggregated [OK]

Hint: Group or aggregate all selected columns [OK]

Common Mistakes:

• Thinking WHERE is required before GROUP BY
• Believing aliasing fixes aggregation errors
• Ignoring GROUP BY rules for selected columns

5. You have a sales table with columns Date (A), Product (B), and Sales (C). How would you write a QUERY to find total sales per product for sales greater than 100, sorted by total sales descending?

hard

A. =QUERY(A1:C100, "SELECT Col2, SUM(Col3) GROUP BY Col2 WHERE Col3 > 100 ORDER BY SUM(Col3) DESC")

B. =QUERY(A1:C100, "SELECT Col2, SUM(Col3) ORDER BY SUM(Col3) DESC WHERE Col3 > 100 GROUP BY Col2")

C. =QUERY(A1:C100, "SELECT Col2, SUM(Col3) WHERE Col3 > 100 ORDER BY SUM(Col3) DESC GROUP BY Col2")

D. =QUERY(A1:C100, "SELECT Col2, SUM(Col3) WHERE Col3 > 100 GROUP BY Col2 ORDER BY SUM(Col3) DESC")

Solution

1. Step 1: Identify correct QUERY clause order

   The correct order is SELECT, WHERE, GROUP BY, ORDER BY.

2. Step 2: Check each option's clause order and syntax

   =QUERY(A1:C100, "SELECT Col2, SUM(Col3) WHERE Col3 > 100 GROUP BY Col2 ORDER BY SUM(Col3) DESC") follows correct order and syntax. Options A, C, and D have clauses in wrong order causing errors.

3. Final Answer:

   =QUERY(A1:C100, "SELECT Col2, SUM(Col3) WHERE Col3 > 100 GROUP BY Col2 ORDER BY SUM(Col3) DESC") -> Option D

4. Quick Check:

   Clause order: SELECT, WHERE, GROUP BY, ORDER BY [OK]

Hint: Remember QUERY clause order: SELECT, WHERE, GROUP BY, ORDER BY [OK]

Common Mistakes:

• Placing WHERE after GROUP BY or ORDER BY
• Forgetting to group by product when summing sales
• Incorrect clause order causing syntax errors