QUERY Function in Google Sheets
The QUERY function is considered to be the most powerful function in Google Sheets.
It can replace many other functions like IF, FIND, VLOOKUP, SEARCH and others because it can perform queries that are not possible or not so easy to do with other functions.
When using the QUERY function, you have to write the queries with the Google Visualization API Query Language, which is a special database-like language. This language is used to ask details about the data set, so you can look up and filter your data in any format.
It’s very similar to the SQL (Structured Query Language), the widely used database language.
What is the QUERY Function?
What is the QUERY function you ask?
The QUERY function in Google Sheets is like bringing a gun to a knife fight. Scrap that. It’s not a gun… it’s a cannon.
The syntax of the QUERY function is:
=QUERY(data, query, [headers])
The function takes two required arguments and the third one is optional:
- The first argument is data which the range of cells from where you query the data.
- The second argument, query, contains the actual query that specifies what you’re looking for. It must be written in the query language and enclosed in double-quotes. It’s the main part of the QUERY function.
- The optional headers argument indicates the number of header rows in your data. It tells the function how many header rows should be excluded from the search. If you don’t set anything, Google Sheets guesses the value based on the content of data.
In queries, you need to use clauses. The clauses tell the query what to do with the data and what you want the function to return.
The SELECT clause is the first clause that you start your queries with. It specifies which columns you want to return and in which order.
For example, “SELECT B D G” returns the results from the columns B, D and G. “SELECT *” returns all the columns of the sheet.
The most common clause is the WHERE clause.
It returns only the rows that match a condition, so it works as a filter. The function searches for the results according to this filter and returns the values that meet your conditions. The WHERE clause works with logical operators (<, >, <=, =>, =, <>) or string operators (contains, starts with, matches, etc.).
The WHERE clause determines from which row is the result returns, while the SELECT clause determines from which columns.
Where to use QUERY for marketing stuff?
- Quickly sorting and filtering large datasets.
- Analysing crawl data.
- Backlink auditing.
- Looking up keywords based on specific criteria
- And lots more…
How To Use The QUERY Function in Google Sheets
The QUERY function in Google sheets uses a type of SQL (usually a database language) to wrangle spreadsheet data.
There are various key commands (which need to be done in the right order) to pull data.
We’ll walk through some of these examples below.
The Google Sheets Query function does the same job as other formulas (like FILTERs, AVERAGEs, and SUMs) but within just one formula string.
Useful QUERY functions:
- SELECT all the data:
- SELECT specific columns only:
=QUERY(countries,"SELECT B, D",1)
- WHERE clause:
=QUERY(countries,"SELECT B, D WHERE D > 100000000",1)
- ORDER BY clause:
=QUERY(countries,"SELECT B, C, D ORDER BY D ASC",1)
- LIMIT clause:
=QUERY(countries,"SELECT B, C, D ORDER BY D ASC LIMIT 10",1)
- Arithmetic functions:
=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100",1)
- LABEL clause:
=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100 LABEL (D / 7162119434) * 100 'Percentage' ",1)
- Aggregation functions:
=QUERY(countries,"SELECT max(D), min(D), avg(D)",1)
- GROUP BY clause:
=QUERY(countries,"SELECT C, count(B) GROUP BY C",1)
There are a few subtle differences to using queries though. Let’s take a look:
QUERY Formula Examples
Now, let’s use these clauses in some examples.
The first QUERY function searches for keywords that had more than 1500 impressions.
The function is as follows:
=QUERY(A2:E16,"SELECT A WHERE B > 1500")
The data is the range where all the information is located. The query argument tells the function to look for the rows where column B is greater than 1500 and return the content of column A from these rows.
In this example, it returns the keywords with more than 1500 impressions.
The power of the QUERY function lies in the layering possibilities. You can add endless other criteria to your queries.
For example, add a new criterion to the above function and return the keywords that have more than 1500 impressions and more than 10 organic clicks.
It’s pretty easy with the QUERY function, you just have to add the new condition to the where clause.
Since the results should meet both of the conditions, they need to be connected with an AND relation. If you’re looking for either more than 1500 impressions or more than 10 organic clicks, you can use the OR relation.
The extended function is:
=QUERY(A2:E16,"SELECT A WHERE B > 1500 AND D > 10")
So far the functions used logical operators. But you can write queries with string operators as well.
Let’s look up the keywords that start with the word “web” and return all the details of these keywords. The WHERE clause can be used with the “starts with” function in the following way:
=QUERY(A1:E16,"SELECT * WHERE A starts with 'how'", 1)
As you can see, the header line is included here, so the data is the range of A1:E16 and the third argument is set to 1.
This way the queried data has headers in order to better visualize the results. The SELECT clause is followed by the “*” character to return all the columns.
The results contain all the details of the keywords starting with “how”.
These filters are useful, but they can be still improved by using more clauses in the query.
The ORDER BY clause is responsible for the sorting of the query results.
To define the way you want to sort your results, use the ORDER BY function followed by the sorting column and the sorting direction. The sorting direction can be either “ASC” for ascending or “DESC” for descending order.
A possible sorting in the previous example is to sort by the number of clicks in descending order. The ORDER BY C DESC.clause at the end of the query takes care of it.
So the whole QUERY function is:
=QUERY(A1:E16,"SELECT * WHERE A starts with 'how' ORDER BY C DESC", 1)
Look closer and you can see that the results are ordered by the number of clicks indeed.
Apart from the column definitions, the SELECT clause can be followed by aggregate functions as well. These are functions that calculate something, such as SUM, AVG, MIN or MAX.
For example, you can return the average of the queried results. Suppose you want to output the average conversions for keywords whose organic clicks are not zero.
The following function calculates it:
=QUERY(A2:E16,"SELECT AVG(E) WHERE D <> 0")
The WHERE clause filters the results similarly as shown before, searching for the rows where the column D is not 0.
The SELECT clause has the AVG(E) attribute which means that it takes the filtered rows and counts the average of the numbers in column E from these rows.
Similarly, you can perform any calculations with the data, just replace the AVG function with the function you choose and filter the results with the right WHERE clause.
Apart from the SELECT, WHERE and ORDER BY clauses, there are 7 other clauses in the query language such as GROUP BY, OFFSET, LIMIT and so on.
Each clause has its special features that make the QUERY function so powerful, so it’s worth looking deeper into them.
Getting to grips with the QUERY function might seem daunting at first, but it really isn’t.
Have a play around with it, try and understand it, write some basic SQL code, break some SQL code and repeat until it doesn’t break (as much).
You’ll thank yourself later when you are wrangling data like a pro.
Still want to QUERY more!?
Here’s a bunch more tutorials to feast on:
- How to Select Multiple Columns
- Select Rows that Contain String
- How to Use “Not Equal” in Query
- How to Create a Pivot Table
- How to Use Order By
- How to Use Group By
- How to Filter by Date Range
- How to Use TODAY() Function in Query
- How to Use the COUNT Function
- How to Use the SUM Function
- How to Extract Top N Values from Range
- How to Return Only Unique Rows
- How to Query From Another Sheet
- How to Query From Multiple Sheets
- How to Use CONCAT with QUERY
- How to Use IMPORTRANGE with Conditions
- Use IMPORTRANGE with Multiple Sheets
- How to Query Using Month
- How to Use Cell Reference in Formula
- How to Use Multiple Criteria in Query
- How to Use Wildcard Characters
- How to Use the Label Clause