GROUP BY with aggregation 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 report showing total sales and average sales per region.

📊 Data: You have a sales data table with columns: Date, Region, Salesperson, and Sales Amount.

🎯 Deliverable: Create a summary table that groups data by Region and shows Total Sales and Average Sales for each region.

Sample Data

Date	Region	Salesperson	Sales Amount
2024-06-01	North	Alice	500
2024-06-02	South	Bob	300
2024-06-03	North	Charlie	700
2024-06-04	East	Diana	400
2024-06-05	South	Eva	600
2024-06-06	East	Frank	200
2024-06-07	North	Grace	300
2024-06-08	West	Hank	450
2024-06-09	West	Ivy	550
2024-06-10	South	Jack	400

1

Step 1: Select a blank area in your sheet to create the summary table.

No formula needed for this step.

Expected Result

You have space ready to build the summary.

2

Step 2: Enter the formula to group data by Region and calculate total and average sales.

=QUERY(A1:D11, "select B, sum(D), avg(D) group by B label sum(D) 'Total Sales', avg(D) 'Average Sales'", 1)

Expected Result

A table showing each Region with its Total Sales and Average Sales.

3

Step 3: Format the Total Sales and Average Sales columns as currency for better readability.

Select the Total Sales and Average Sales columns, then Format > Number > Currency.

Expected Result

Sales amounts appear with currency symbols and two decimal places.

Final Result

Region   | Total Sales | Average Sales
-------------------------------------
East     | $600.00     | $300.00
North    | $1,500.00   | $500.00
South    | $1,300.00   | $433.33
West     | $1,000.00   | $500.00

✓North region has the highest total sales of $1,500.

✓South region has an average sale of about $433.33, which is lower than North and West.

✓East region has the lowest total sales but a moderate average sale.

Bonus Challenge

Add a column to the summary that shows the number of sales transactions per region.

Show Hint

Modify the QUERY formula to include count(D) and label it as 'Number of Sales'.