This tutorial is about language clauses in the Google Sheets Query function.

They are Select, Where, Group By, Pivot, Order By, Limit, Offset, Label, and Format. I have presented these clauses as per their correct clause Order in Google Sheets Query.

I have excluded the clauses “From,” “Skipping,” and “Options.” These clauses are either eliminated or not in regular use.

Please note that I am not talking about Google Sheets Query order of execution but their order (precedence of occurrence) in the formula.

What is the correct clause order in Google Sheets Query? are they as per the above-given order?

Yes! The above is the correct order to use language clauses in Google Sheets Query.

But as you may know, we don’t require to use all of them in a formula.

Let’s learn how to correctly use the language clauses (keywords or keyword combinations) as per their order in a Google Sheets Query formula.

How to Use Correct Clause Order in a Google Sheets Query Formula

Query Syntax: QUERY(data, query, [headers])

Here is our sample data (Statement of Accounts aka SOA).

We can manipulate this data using Query formulas that involve different keywords.

I am purely concentrating on how to write a Query formula with the correct clause or keyword order.

The first clause to use in Query is SELECT.

1. SELECT: Keyword for Selecting Columns

The following formula with Query SELECT clause returns the ‘Customer’ and ‘Balance’ columns from the above data (SOA).

=query(A1:F,"Select A,F")

As mentioned above, SELECT is the first clause in Query in order. Then the WHERE clause follows.

2. WHERE: Keyword to Specify Conditions to Filter

The WHERE clause comes second in the order. The SELECT clause is optional with the WHERE clause.

Problem: Return the rows in columns A and F where the customer name in column A is “Customer 1.”

=query(A1:F,"Select A,F Where A='Customer 1'")

If you do not want to select any particular columns in the formula, you can omit the SELECT clause use.

See the following example, which filters ‘Customer 1’ as above but returns all the columns in the range.

=query(A1:F,"Where A='Customer 1'")

If you aren’t familiar with values used for comparisons or assignments, please check this: Examples of the Use of Literals in Query in Google Sheets.

3. GROUP BY: Keyword Combination for Grouping Rows

GROUP BY is the third clause to use in the correct clause order in Google Sheets Query.

The WHERE clause is optional with the GROUP BY clause.

Use only the columns specified in the SELECT clause in the GROUP BY clause.

=query(A1:F,"Select A, Sum(F) Where A='Customer 1' group by A")

The above formula would return the below result.

As far as I know, the SELECT clause should be present when using GROUP BY in the formula.

But you have the freedom not to specify the column/columns.

The following Query formula would only return the ‘sum Balance’ column above.

=query(A1:F,"Select Sum(F) Where A='Customer 1' group by A")

You can skip using the WHERE clause with the GROUP BY clause.

=query(A1:F,"Select A, Sum(F) group by A")

It will return the output as per the table below.

Why does the blank row appear at the top of the Query formula result?

It’s because we have blank rows in our data range A1:F. Either use A1:F10 or use the WHERE clause within Query.

=query(A1:F,"Select A, Sum(F) Where A<>'' group by A")
=query(A1:F,"Select A, Sum(F) Where A<>'' group by A Order by A Desc")

4. Pivot: Keyword to Pivot Data

The PIVOT is the fourth clause in the correct clause order to use in Google Sheets Query.

Here also, the WHERE keyword is not a must with PIVOT as it’s essentially for filtering data but recommended to filter out blanks when your data is open (A1:F instead of A1:F10).

=query(A1:F,"Select A, Sum(D) Where A<>'' group by A Pivot C")

See one more example.

=query(A1:F,"Select A, Sum(D) Where A<>'' group by A Pivot month(C)")

5. ORDER BY: Keyword Combination for Sorting

ORDER BY is the fifth keyword in the correct clause order in Google Sheets Query.

All the preceding clauses (keywords or keyword combinations) are optional with ORDER BY.

Below find a few Query formula examples of using the ORDER BY clause without other preceding keywords or keyword combinations.

Query Function ORDER BY without Other Preceding Clauses:

=query(A1:F,"Order by A")
=query(A1:F,"Order by A,C")
=query(A1:F,"Order by A Asc")
=query(A1:F,"Order by A Desc")

Query Function ORDER BY with Other Preceding Clauses:

=query(A1:F,"Select A,E order by A Asc")
=query(A1:F,"Select A,E Where A is not null order by A Asc")
=query(A1:F,"Select A, Sum(F) Where A<>'' group by A Order by A Desc")
=query(A1:F,"Select A, Sum(D) Where A<>'' group by A Pivot month(C) Order By A Desc")

6. LIMIT: Keyword to Limit or Constrain the Number of Rows

Find below the examples for the correct clause order of the LIMIT clause in Google Sheets Query.

The LIMIT clause is independent and doesn’t require other clauses.

Here are example formulas, with or without using other clauses with LIMIT.

Query Function LIMIT without Other Preceding Clauses:

Limit the number of rows:

=query(A1:F,"Limit 5")

Query Function LIMIT with Other Preceding Clauses:

Query to limit the number of rows and columns.

=query(A1:F,"Select A,E Limit 5")
=query(A1:F,"Select A,E Where A<>'' Limit 5")
=query(A1:F,"Select A, Sum(F) Where A<>'' group by A Limit 1")
=query(A1:F,"Select A, Sum(D) Where A<>'' group by A Pivot C Limit 1")
=query(A1:F,"Select A, Sum(D) Where A<>'' group by A Pivot month(C) Order By A Desc Limit 1")

7. OFFSET: Keyword to Offset Rows

Does the Query OFFSET cLause require the other clauses to work?

Nope! The OFFSET clause will work stand-alone.

=query(A1:F,"Offset 1")

When we use other clauses with the OFFSET keyword, we must follow the correct clause order.

If we follow a different clause order, Google Sheets Query will return the #VALUE error.

It applies to all clauses!

The Correct Clause Order When Using the OFFSET Clause in Google Sheets Query:

=query(A1:F,"Select A,E Offset 2")
=query(A1:F,"Select A,E Where A<>'' Offset 2")
=query(A1:F,"Select A, Sum(F) Where A<>'' group by A Offset 1")
=query(A1:F,"Select A, Sum(D) Where A<>'' group by A Pivot C Offset 1")
=query(A1:F,"Select A, Sum(D) Where A<>'' group by A Pivot month(C) Order By A Desc Offset 1")
=query(A1:F,"Select A, Sum(D) Where A<>'' group by A Pivot month(C) Order By A Desc Limit 1 Offset 1")

8. Label: Keyword to Customize Field Labels

The LABEL clause in Query doesn’t need the support of other clauses to work.

I hope the below Query formula example itself is self-explanting.

=query(A1:F,"label A 'Name', B 'Inv'")

What about using the LABEL clause with other clauses preceding?

=query(A1:F,"Select A,E Label E 'Amt Cr'")
=query(A1:F,"Select A,E Where A<>'' Label E 'Amt Cr'")
=query(A1:F,"Select A, Sum(F) Where A<>'' group by A Label Sum(F) 'Total Balance'")
=query(A1:F,"Select A, Sum(D) Where A<>'' group by A Pivot C Label A'Name'")
=query(A1:F,"Select A, Sum(D) Where A<>'' group by A Pivot month(C) Order By A Desc Label A 'Name'")
=query(A1:F,"Select A, Sum(D) Where A<>'' group by A Pivot month(C) Order By A Desc Limit 1 Label A'Name'")
=query(A1:F,"Select A, Sum(D) Where A<>'' group by A Pivot month(C) Order By A Desc Limit 1 Offset 1 Label A 'Name'")

9. Format: Keyword to Specify Format Patterns

To learn this clause, please read my post – How to Format Date, Time, and Number in Google Sheets Query.

We can use the FORMAT clause in Google Sheets Query with or without other clauses.

=query(A1:F,"format C 'dd-mmm'")

FORMAT is the last clause in the Query clause order. You can close the ‘query’ syntax with it, followed by the header as below.

Query Function Syntax:
QUERY(data, query, [headers])

=query(A1:F,"Select A, Sum(D) Where A<>'' group by A Order By A Desc Limit 1 Offset 1 Label A 'Name' Format Sum(D) '$##.00'",1)

I have skipped the PIVOT clause in the above Query formula as formatting a Pivot header needs a workaround.

Do you know how to format Google Sheets Query Pivot labels?

If not, you can read this guide – How to Format Query Pivot Header Row in Google Sheets.

You may bookmark this webpage to check the correct clause order in Google Sheets Query as and when required.