QUERY in Google Sheets
Runs a query on a range with select, where, group by and order by, and returns the result as a table.
Syntax
QUERY(data, query, [headers])- data
- The range to query. Its columns are called A, B, C… after the sheet letters.
- query
- The query as text in quotes, such as "select A, B where C > 100".
- headers (optional)
- The number of header rows in the data. Left out, Google Sheets guesses.
Examples
E1
=QUERY(1A1:C6,2"select A, sum(C) group by A")| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Rep | Amount | Region | sum Amount | |
| 2 | North | Ann | 120 | North | 320 | |
| 3 | South | Bo | 80 | South | 230 | |
| 4 | North | Cy | 200 | West | 50 | |
| 5 | West | Di | 50 | |||
| 6 | South | Ed | 150 |
In plain words: From 1the table in A1:C6, 2list each region in column A with the total of column C.
E1=QUERY(A1:C6,"select B where C > 100 order by C desc")
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Rep | Amount | Rep | |
| 2 | North | Ann | 120 | Cy | |
| 3 | South | Bo | 80 | Ed | |
| 4 | North | Cy | 200 | Ann | |
| 5 | West | Di | 50 | ||
| 6 | South | Ed | 150 |