Google Sheets lesson 10 of 11, Sheets power tools
QUERY: group by, totals and labels
Turn a long list into a summary table, one row per store or rep, with a single formula.
Watch it done
4 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
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
=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, Col2Watch 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
- 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.
- 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".
- 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".
The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.
- Totals by store
- Ranked, filtered and labeled
- Two columns, by position