# QUERY: group by, totals and labels (Google Sheets lesson 13 of 17)

> Turn a long list into a summary table, one row per store or rep, with a single formula.

Source: https://spreadsheetpractice.com/google-sheets/learn/query-group-by · Updated October 29, 2026

- **Course:** [Google Sheets course](https://spreadsheetpractice.com/google-sheets), track "Sheets power tools"
- **Time:** about 12 minutes, 3 graded exercises
- **Functions:** `QUERY`, `group by`, `sum`, `count`, `label`, `Col1`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=QUERY(A1:E10,"select A, sum(E) group by A",1)`: One row per store with its total

## How it works

Add `group by` to a query and QUERY returns one row for each value in that column, with figures worked out for each group: `select A, sum(E) group by A` lists every store once with its sales added up. Use `sum`, `avg`, `count`, `min` or `max` on the other columns. Every column you select must either appear in the group by or sit inside one of these functions.

The groups come out sorted by the grouped column. To rank them, sort by the total itself: `order by sum(E) desc`. A `where` clause goes before `group by` and drops rows before anything is added up, so `where B != 'Merch'` leaves merchandise out of every total.

QUERY names a total column after the function and the header, such as "sum Sales". `label sum(E) 'Total sales'` sets your own header instead. The label clause comes after order by and limit, and it must name the column exactly as select does.

When the data is an array you build with braces, such as `{A1:A10, D1:D10}`, its columns no longer have sheet letters. Refer to them by position instead: `Col1` is the first column of the array, `Col2` the second, and so on.

## Syntax

| Formula | What it does |
|---|---|
| `=QUERY(A1:E10,"select A, sum(E) group by A",1)` | One row per store with its total |
| `=QUERY(A1:E10,"select A, count(E), avg(E) group by A",1)` | A count and an average for each group |
| `=QUERY(A1:E10,"select B, max(E) where A = 'Old Town' group by B",1)` | Filter the rows first, then group |
| `=QUERY(A1:E10,"select C, sum(E) group by C order by sum(E) desc",1)` | Rank the groups by their total |
| `=QUERY(A1:E10,"select A, sum(E) group by A label sum(E) 'Sales'",1)` | Your own header for the total |
| `=QUERY({A1:A10,D1:D10},"select Col1, sum(Col2) group by Col1",1)` | Columns of an array are Col1, Col2 |

## Watch out for

- Selecting a column that is neither grouped nor inside sum, count or another aggregate gives an error.
- There is no clause for filtering on a total. To keep only big totals, put the grouped QUERY inside another QUERY or a FILTER.
- The label must name the column exactly as select does: label sum(E) 'Sales', not label E 'Sales'.
- Letters stop working once the data is an array in braces. select A on {A1:A10,D1:D10} is an error; use Col1.

## The exercises

Solve them in the live grid at [QUERY: group by, totals and labels](https://spreadsheetpractice.com/google-sheets/learn/query-group-by). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Totals by store.** In G1, use QUERY on A1:E10 to return each store once, with its total sales and its number of entries, in that order. Count the Sales column (E) for the number of entries, and pass 1 for headers.
2. **Ranked, filtered and labeled.** In G1, rank the reps (column C) by their food and drink sales: leave out the Merch category, total column E for each rep, put the biggest total first, and label the total column "Food and drink".
3. **Two columns, by position.** Only the store (A) and units (D) matter here. In G1, build the array {A1:A10,D1:D10} inside QUERY and total the units for each store, biggest first. Refer to the columns as Col1 and Col2, pass 1 for headers, and label the total "Units".

Previous: [QUERY basics: select, where, order by](https://spreadsheetpractice.com/google-sheets/learn/query-function-basics) · Next: [QUERY: pivot, limit and text matching](https://spreadsheetpractice.com/google-sheets/learn/query-pivot-limit-contains)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
