QUERY group by practice exercises
QUERY with group by turns a list into a summary table, like a pivot table built from one formula. Combine it with sum(), count(), avg(), label to rename headers, and order by / limit for top-N lists.
Syntax
=QUERY(data, "select B, sum(F) group by B order by sum(F) desc limit 3")
group by B- One output row per distinct value in column B.
sum() / count() / avg() / max() / min()- How to combine the rows in each group.
label sum(F) 'Total'- Renames the output column header.
The practice data
An orders table: who sold what, where, and for how much. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Product | Units | Price | Sales |
| 2 | Ana | East | Widget | 12 | 5 | 60 |
| 3 | Ben | West | Gadget | 5 | 20 | 100 |
| 4 | Cara | East | Gadget | 8 | 20 | 160 |
| 5 | Dev | North | Widget | 20 | 5 | 100 |
| 6 | Ana | West | Gizmo | 3 | 50 | 150 |
| 7 | Ben | East | Widget | 7 | 5 | 35 |
| 8 | Cara | North | Gizmo | 2 | 50 | 100 |
| 9 | Dev | East | Gadget | 6 | 20 | 120 |
Worked example
=QUERY(A1:F9,"select C, sum(D) group by C")
Total Units for each Product. Result:
| Product | sum Units |
| Gadget | 19 |
| Gizmo | 5 |
| Widget | 39 |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 Return the number of orders per Rep.
2 Return the average Sales per Region, with the header Average sales.
3 Return the top 2 Reps by total Sales, highest first.
Master QUERY group by in the full course
Lesson 17 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.
Start free See pricingCommon QUERY group by mistakes
- Selecting a column that is neither grouped nor inside an aggregate, which gives an error.
- Putting
labelbeforeorder by. The clause order is select, where, group by, pivot, order by, limit, label. - Ordering by the column letter (order by F) instead of the aggregate (order by sum(F)).
FAQ
Is QUERY group by like a pivot table?
Yes, and it updates automatically. Add pivot to spread a second column across the top.
Can I filter groups after aggregating?
QUERY has no HAVING clause. Wrap it in another QUERY or FILTER the result.