QUERY function basics in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a summary of total sales by product category for the first quarter.

📊 Data: You have a sales data table with columns: Date, Product, Category, Quantity Sold, and Sales Amount.

🎯 Deliverable: Create a summary table showing each product category and the total sales amount for January to March.

Sample Data

Date	Product	Category	Quantity Sold	Sales Amount
2024-01-05	Shirt	Clothing	10	200
2024-01-15	Jeans	Clothing	5	250
2024-02-10	Blender	Appliances	3	150
2024-02-20	Toaster	Appliances	4	120
2024-03-05	Notebook	Stationery	20	100
2024-03-15	Pen	Stationery	50	75
2024-04-01	Jacket	Clothing	2	180
2024-04-10	Microwave	Appliances	1	300

1

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

No formula needed.

Expected Result

Data range selected for analysis.

2

Step 2: Use the QUERY function to sum sales by category for dates in the first quarter (January to March).

=QUERY(A1:E9, "select C, sum(E) where A >= date '2024-01-01' and A <= date '2024-03-31' group by C", 1)

Expected Result

A summary table with categories Clothing, Appliances, Stationery and their total sales amounts: Clothing 450, Appliances 270, Stationery 175.

3

Step 3: Place the QUERY formula in a new sheet or below the data to display the summary.

Use the formula from step 2 in cell G1 or another empty cell.

Expected Result

Summary table appears showing total sales by category for Q1.

Final Result

Category    | Total Sales
-------------------------
Clothing   | 450
Appliances | 270
Stationery | 175

✓Clothing category had the highest sales in the first quarter with $450.

✓Appliances and Stationery had lower sales, $270 and $175 respectively.

✓Sales outside the first quarter are excluded from this summary.

Bonus Challenge

Modify the QUERY formula to also show the total quantity sold per category for the first quarter.

Show Hint

Add sum(D) to the select statement.