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.

ABCDEF
1RepRegionProductUnitsPriceSales
2AnaEastWidget12560
3BenWestGadget520100
4CaraEastGadget820160
5DevNorthWidget205100
6AnaWestGizmo350150
7BenEastWidget7535
8CaraNorthGizmo250100
9DevEastGadget620120

Worked example

=QUERY(A1:F9,"select C, sum(D) group by C")

Total Units for each Product. Result:

Productsum Units
Gadget19
Gizmo5
Widget39

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.

H2

2 Return the average Sales per Region, with the header Average sales.

H2

3 Return the top 2 Reps by total Sales, highest first.

H2

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 pricing

Common QUERY group by mistakes

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.

Keep practicing