Google Sheets QUERY Function: Clauses, Order and Examples

Three arguments, and only the first two are required

Google's help page gives the syntax as QUERY(data, query, [headers]). The data is the range you want to ask questions about. The query is a text string, either typed in quotation marks or read from a cell. The third argument, headers, is the number of header rows at the top of the data.

Leave headers out, or set it to -1, and Sheets guesses. A guess can go wrong when the first data row looks like a header, so write the number yourself when the answer matters. Google's two sample calls show both styles: QUERY(A2:E6,"select avg(A) pivot B") and QUERY(A2:E6,F2,FALSE), where F2 holds the query text.

The language is not SQL, and the clause order is strict

The query string is written in the Google Visualization API Query Language, version 0.7 in the reference read for this page. It looks like SQL, but there is no from clause, because the data argument already says where the rows come from.

Clauses are optional and separated by spaces. When you use several, they must appear in this order:

• select: which columns, in which order. Leave it out, or write select *, to get every column.
• where: which rows to keep.
• group by: one output row for each distinct value.
• pivot: turn the distinct values of a column into new columns.
• order by: sort the result.
• limit: the most rows to return.
• offset: how many rows to skip first.
• label: rename output columns.
• format: set display patterns for numbers and dates.
• options: no_format or no_values.

Columns are letters, not header names

In a query on a sheet range, columns are named by their letters: A, B, C. The text in your header row does not work as a column name. Google says QUERY accepts either the Col notation (Col1, Col2) or the A, B notation, so you can pick whichever reads better.

An identifier that contains a space, starts with a digit, or is a reserved word needs back-quotes. Single quotes do not work for this. The reference lists 23 reserved words, among them date, label, order and format. Identifiers and text values are case-sensitive, while the language keywords are not.

A worked example: totals per region from 6 rows

Take a sales table in A1:C7 with headers Region, Rep and Amount. Rows 2 to 7 hold: North, Ann, 120; South, Bo, 80; North, Cy, 200; East, Di, 150; South, Eli, 90; North, Fay, 60. You want the total per region, largest first, for amounts of at least 80.

The formula is =QUERY(A1:C7, "select A, sum(C) where C >= 80 group by A order by sum(C) desc label sum(C) 'Total'", 1).

Walk through it. The where clause drops Fay's 60, leaving 5 rows. The group by gives one row for each of the 3 regions. North becomes 120 + 200 = 320, East is 150, and South is 80 + 90 = 170. Order by sum(C) desc puts North first, then South, then East. Label changes the heading of the second column to Total. The final 1 tells Sheets that row 1 is the only header row.

Notice the clause order in the text: select, where, group by, order by, label. Putting label before order by returns a parse error.

Where, strings and dates have their own rules

The where clause takes <=, <, >, >=, =, != and <>. Equality is a single =, so == fails. Test for blanks with is null or is not null, and combine tests with and, or and not.

For text, the language offers contains, starts with, ends with, matches and like. Matching is case-sensitive. The matches operator needs a regular expression that fits the whole string, so it is not a search inside the text. The like operator uses % and _ as wildcards.

Dates must be written as a literal: date "2008-03-18". The year-month-day shape is fixed.

Aggregations: five functions and one restriction

The language has 5 aggregation functions: avg, count, max, min and sum. Count skips nulls. They can appear in select, order by, label and format, but not in where. To filter on a total you cannot write where sum(C) > 100, because aggregations are not allowed there, and the language has no having clause.

Each aggregation takes one column identifier. Both max(year(startDate)) and sum(salary + perks) are invalid. Add a helper column for the sum, or wrap the whole QUERY in another QUERY.

Pivot turns values into columns

Google's sample select avg(A) pivot B shows the idea. The distinct values of column B become column headings, and the aggregation fills the cells. Without group by, a pivot returns exactly one row.

A pivot column may not also appear in select, group by or order by, and once you pivot, order by cannot contain aggregation columns.

Date and text functions, including the month trap

The scalar functions are year, month, day, hour, minute, second, millisecond, quarter, dayOfWeek, now, dateDiff, toDate, upper and lower. Two of them surprise people. The month function is zero-based, so January is 0 and December is 11. The dayOfWeek function is one-based, with 1 meaning Sunday.

Mixed column types turn values into blanks

Each column in the data can hold only boolean, numeric or text values, and dates and times count as numeric. If a column mixes types, the majority type decides the column type, and values of the minority type are treated as null.

In practice, a column of 40 amounts with 3 numbers stored as text will silently drop those 3 from a sum. Fix the source cells, for example by converting the text to numbers, rather than changing the query.

Common mistakes and what they look like

Using == instead of = fails. Wrapping a text value in double quotes inside a double-quoted query ends the string early. Writing label before order by breaks the clause order. Asking for a header name instead of a letter does not work. Limiting rows with limit 15 offset 30 returns rows 31 to 45, because offset is applied first, which surprises people who expect the limit to apply first.

If a formula gives an error and you cannot see why, Crade can read the screen and explain the message.

Who QUERY suits

QUERY suits people who want filtering, grouping and sorting in one cell and who can learn about 10 keywords. It does not suit someone who needs a filter on a computed total, where a helper column or a pivot table is simpler, since the language has no having clause. Sheets files can hold up to 20 million cells or 100MB according to Google's file limits page, so very large sources are better handled in a database.