SELECT clause in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a simple report showing only the product names and their total sales from the sales data.

📊 Data: You have a table with columns: Date, Product, Region, Units Sold, and Total Sales.

🎯 Deliverable: Create a new table that lists each product and the total sales amount for that product.

Sample Data

Date	Product	Region	Units Sold	Total Sales
2024-05-01	Apples	North	10	50
2024-05-02	Bananas	South	5	15
2024-05-03	Apples	East	8	40
2024-05-04	Oranges	West	12	60
2024-05-05	Bananas	North	7	21
2024-05-06	Oranges	South	9	45
2024-05-07	Apples	West	6	30
2024-05-08	Bananas	East	4	12

1

Step 1: Select a blank area in your sheet to create the report. In the first cell, type the header 'Product'. In the next cell to the right, type 'Total Sales'.

Expected Result

Headers 'Product' and 'Total Sales' appear in two adjacent cells.

2

Step 2: Use the QUERY function to select the product names and sum of total sales grouped by product. Enter this formula below the headers:

=QUERY(A1:E9, "SELECT B, SUM(E) GROUP BY B", 1)

Expected Result

A table appears listing each product once with the total sales summed: Apples 120, Bananas 48, Oranges 105.

3

Step 3: Check that the results show each product only once with the correct total sales amount.

Expected Result

The table shows: Apples | 120, Bananas | 48, Oranges | 105.

Final Result

Product    | Total Sales
------------------------
Apples     | 120
Bananas    | 48
Oranges    | 105

✓Apples have the highest total sales of 120.

✓Bananas have the lowest total sales of 48.

✓Oranges sales are in the middle with 105.

Bonus Challenge

Modify the QUERY formula to show only products with total sales greater than 50.

Show Hint

Add a HAVING clause to the QUERY: "SELECT B, SUM(E) GROUP BY B HAVING SUM(E) > 50"