QUERY Function – Google Sheets

The QUERY function in Google Sheets uses SQL-like syntax to analyze your spreadsheet data and return it in a new table. While the addition of the SQL syntax provides this function with flexibility, it also makes it harder to learn.

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 they 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.

Syntax

=QUERY(data, query, [headers])

  • data – Location of the data
  • query – 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 meet
    • GROUP BY – Aggregates values
    • PIVOT – Aggregates values into new columns
    • ORDER BY – Sorts rows by values
    • LIMIT – Limits the number of rows returned
    • OFFSET – Skips a specified number of rows
    • LABEL – Creates column labels
    • FORMAT – Formats outputs
    • OPTIONS – Sets additional options
  • [headers] – The number of header rows at the top of the data

Examples

Example 1 – Return Values with Matching Words

Inventory listing showing items from three warehouses
Inventory Listing to be queried

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)

Inventory listing showing items from only the North warehouse
Query Output Showing Only Items in the North Warehouse

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

Inventory listing showing items from three warehouses
Inventory Listing to be queried

Next, we will be using the same inventory listing, and we want to return all inventory items with less than 140 items at any warehouse.

=QUERY(A1:D13,"SELECT * WHERE (B*C<140)'",1)

Inventory listing showing items with quantities over 140 resulting from the QUERY function
Query Output Showing Only Items Under 140

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, there is no column for the total amount of units in the source table. 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

Inventory listing showing items from three warehouses
Inventory Listing to be queried

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 was not showing the total values in the source or output tables. Now we are going to show that value. Also, as the table is getting 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 in single quotes since it is a string.

=QUERY(A1:D13, "SELECT A, B, C, (B*C) LABEL (B*C) 'Total'",1)

Output of the QUERY function with a new column labeled Total
QUERY Output Showing The New Total Column

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)

QUERY Output Showing Only Item Amounts Under 140

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

Inventory listing showing items from three warehouses
Inventory Listing to be queried

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)

Inventory listing showing items with quantities over 140 and only from the North warehouse as output from the QUERY function
Query Output Limiting Quantity and Location

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.

Inventory listing showing items from three warehouses
Inventory Listing to be queried

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)

Table of data showing only top five amounts per case sorted in descending order
Query Output Sorted and Limited

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.

Inventory listing showing items from three warehouses
Inventory Listing to be queried

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)

A table with QUERY output of one column of the items and one column of the sums of the cases
Query Output Summed and Grouped

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 layer of grouping like a pivot table.

=QUERY(A1:D13,"SELECT A, SUM(B) GROUP BY A PIVOT D",1)

Query Output Summed and Grouped

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.

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.

2 thoughts on “QUERY Function – Google Sheets”

  1. Hello, I was trying to get data from sheets, data from a google form, organized in a certain way. I want the data to go to a new sheet if it contains a response value. I have been trying to get this to work but I am not sure how to do this or if it is a possible option. Would you be able to help me get this figured out?

    Reply
    • Hi Jenna. It sounds like you could use the FILTER function on that other Sheet. QUERY is perhaps over powered if that’s all you want to do. Here’s how to reference another sheet or file (it’s a YT video from another channel of mine) https://youtu.be/vBEZiSA49MM.

      Reply

Leave a Comment