The QUERY function in Google Sheets uses SQL-like syntax to analyze and return your spreadsheet data in a new table. While adding the SQL syntax provides this function with flexibility, it also makes learning harder.
The family of FILTER functions in Google Sheets offers a simpler alternative to the QUERY function. But they don’t recognize a header row and are limited in their ability to combine filters, sorts, limits, etc.
Go to this spreadsheet for examples of the QUERY function shown above that you can study and use anywhere you would like.
Contents
- 1 Syntax
- 2 Video Tutorial
- 3 Examples
- 3.1 Example 1 – Return Values with Matching Words
- 3.2 Example 2 – Return Values Less Than a Certain Amount
- 3.3 Example 3 – Return Calculated and Labeled Column
- 3.4 Example 4 – Return Values Less Than a Certain Amount and Matching Words
- 3.5 Example 5 – Sort and Limit the QUERY Responses
- 3.6 Example 6 – Grouping the QUERY Data
- 3.7 Example 7 – Pivoting the QUERY Data
- 3.8 Live QUERY Examples in Sheets
- 4 Conclusion
Syntax
=QUERY(data, query, [headers])
data– Location of the dataquery– The query statement. The query is composed of optional clauses that you must use in order.select– The columns to return. If omitted, the query returns all columns.where– Conditions that rows must meetgroup by– Aggregates valuespivot– Aggregates values into new columnsorder by– Sorts rows by valueslimit– Limits the number of rows returnedoffset– Skips a specified number of rowslabel– Creates column labelsformat– Formats outputsoptions– Sets additional options
[headers]– The number of header rows at the top of thedata
Video Tutorial
Examples
Example 1 – Return Values with Matching Words
This table lists inventory held at three different warehouses. You want to list the items that are in the North Warehouse. We’ll use the following QUERY function.
=QUERY(A1:D13,"SELECT * WHERE D='North'",1)
The output of this QUERY function is a list of only the rows that specify North as the warehouse location.
Let’s break this function down into its parts.
The first part of the function, A1:D13, is the data value. The value given for data is the table’s location on which we want to perform the query. Additionally, you could create a named range for the table, but we’re just using the simple cell reference of A1:D13 to tell Sheets where to look.
Next, notice that double quotes surround the entire query section, and single quotes surround the string value of North since it is a word instead of a number. In other spreadsheet formulas, you should wrap strings with double quotes, but double quotes are used around the entire query leaving you to work with single quotes. Be sure to capitalize the N in North as the input for the clauses is case-sensitive.
The SELECT * clause tells the QUERY function which columns to return. The * lets Sheets know to return all of the columns. If you wanted to identify specific columns, the equivalent SELECT clause would be SELECT A, B, C, D with the column letters used as their identifiers. Note that you do not use the names of the column headers, such as Item or Cases.
The WHERE D='North' clause limits the output to only rows that have the value of North in column D.
The 1 at the end of the function uses the optional [headers] argument to tell Sheets that there is only one header row. Since 1 is the default, this function would operate the same if this argument were left off.
Example 2 – Return Values Less Than a Certain Amount
Next, we will use the same inventory listing, and we want to return all inventory items with less than 140 items to any warehouse.
=QUERY(A1:D13,"SELECT * WHERE (B*C<140)",1)
We will still use SELECT * as we want to return all of the columns from the source table.
This query will introduce two new concepts.
First, we will still use the WHERE clause, but we will use it for a number value instead of a string. Therefore, we will not surround 140 with 's as we did for the Wharehouse name since 140 is a number value.
Second, the source table has no column for the total number of units. Because we need the total amount of units for our QUERY, we will calculate it by multiplying the Cases by the Amt. per Case using the formula B*C as Cases is in column B and Amt. per Case is in column C.
Example 3 – Return Calculated and Labeled Column
You may want to see the result of the B*C calculations performed in example 2 in your table. We limited our results to items with a total count of less than 140. However, the output did not show the total values in the source or output tables. Now, we are going to show that value. Also, as the table gets more crowded, we will exclude the Warehouse column to simplify the table.
To do this, we will change the select portion of the query from SELECT * to SELECT A, B, C, (B*C) and then add in the new LABEL clause. Changing the clause will give the new column with (B*C) a readable label of Total. As you write the formula, notice the word Total will need to be surrounded by single quotes since it is a string.
=QUERY(A1:D13, "SELECT A, B, C, (B*C) LABEL (B*C) 'Total'",1)
Now that we can see the Total column, we will add the WHERE clause back to the query. Adding the Total column enables us to see there are no values left in the Total column over 140.
=QUERY(A1:D13,"SELECT A, B, C, (B*C) WHERE (B*C<140) LABEL (B*C) 'Total'",1)
As you add more criteria to your queries, you will notice that, while the query becomes longer, it does not become a jumble of nested formulas like it would if you were combining other spreadsheet functions such as FILTER and SORTN for an equivalent formula.
Example 4 – Return Values Less Than a Certain Amount and Matching Words
We will remove the Total column from the previous example and its label to keep the function easy to read.
Now we will combine the concepts from the first two examples. We will query the same inventory and return all North warehouse items with a quantity greater than 140. Add both criteria to the WHERE clause with the word AND between them to combine these concepts.
=QUERY(A1:D13,"SELECT * WHERE (B*C<140) AND D='North'",1)
Example 5 – Sort and Limit the QUERY Responses
Let’s look at a few more clauses that can change the query function’s output. We will use the same source table of inventory and remove the WHERE or LABEL clauses for simplicity.
We want to return the five Item values with the greatest Amt. per Case for this query. To do this, we will sort the list by Amt. per Case in descending order and limit the output to five rows. The ORDER BY clause for columns C will sort the data by the contents of column C, and the specification of DESC tells the query to sort in descending order. The default sort order, which you don’t need to specify, is ascending.
=QUERY(A1:D13,"SELECT * ORDER BY C DESC LIMIT 5",1)
Example 6 – Grouping the QUERY Data
Now that we have covered filtering and rearranging the data, we will summarize it. Using the SUM aggregation function combined with the GROUP BY clause, we can arrive at the number of Cases for each Item.
We will use the same source data as the previous exercises.
Column A will be passed to the SELECT and the GROUP BY clauses. Every column listed in the SELECT clause must be used in either the GROUP BY clause or the aggregation function, which is SUM in this case.
=QUERY(A1:D13,"SELECT A, SUM(B) GROUP BY A",1)
Example 7 – Pivoting the QUERY Data
Similar to Example 6, we will be using another clause that groups the data, but this one called PIVOT, spreads the values into new columns for an additional grouping layer like a pivot table.
=QUERY(A1:D13,"SELECT A, SUM(B) GROUP BY A PIVOT D",1)
Live QUERY Examples in Sheets
Go to this spreadsheet for examples of the QUERY function shown above that you can study and use anywhere you would like.
Conclusion
The query function is potent, and we have only scratched the surface of its capabilities. For help with your queries, Google’s query language documentation is an excellent place to start.