Google Sheet QUERY Function – All Variations

Reading Time: 7 minutes

The QUERY function in Google Sheets is used to run SQL queries.

Syntax
=QUERY(data, query, [headers])

data – The range of cells to perform the query on.

query – The query to perform.
The value for query must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.

headers – [Optional] The number of header rows at the top of data.

Sample Usage
=QUERY(Dataset, "Select *")

//This retrieves all values from the data range named “Dataset”.

=QUERY(Dataset, "Select * WHERE D>10")

//Retrieves all values greater than 10 from column D.

Objective

Using the Google Sheet Query function to run SQL queries. All variations in query with examples.

What is Google Sheets Query Function?

The Google Sheets Query function helps you use database type commands which are used in Structured Query Language (SQL) in Google Sheets. The Query function can be used to extract a subset of data from your main dataset, which can help in giving you a better insight into the dataset of interest.

Setting up the data range

1. Selecting the data range:

• We will use a dataset for querying. For ease of querying, we will name the range so we do not need to select the data range every time.
• Select cell A1 and click Ctrl + A (Windows) or Cmd+A (Mac) to select the entire data range.

1. Going to Named Ranges :

• Go to Data
• Select Named Ranges

1. Naming the data range :

• Give a suitable name to your data range. Here I am giving it a Dataset.
• Click on Done to complete the process of naming.

Selecting all data using the Google Sheets Query function

• To retrieve all the data from the table, use :

=QUERY(Dataset, “Select *”)

Figure 4: Query to select all columns of the dataset

• The above query is SQL equivalent to “Select * from Dataset”
• The output will look like this :

Select Specific column only

• To select only specific columns, use this query :

=QUERY(Dataset, “Select A”)

Figure 6: Query to select a specific column

• The above query is equivalent to Select A from Dataset in SQL
• The output of the query will be as shown below :

Select Multiple Columns

• To select multiple specific columns, use the following query :

=QUERY(Dataset, “Select A,C,D,I”)

Figure 8: Query to select multiple columns

• The output of the above query will be :

WHERE clause in Google Sheets Query Function

• If you want to specify a condition for querying, use the WHERE clause, shown below:

=QUERY(Dataset, “Select * WHERE D>10”)

Figure 10: Using WHERE clause in Google Sheets Query formula

• This is SQL equivalent to Select * from Dataset WHERE D>10
• The output of the above query will be :

WHERE clause for multiple conditions in Google Sheets Query function

• If you want to put in multiple conditions in the WHERE clause, use this formula :

=QUERY(Dataset, “Select * WHERE D>10 AND B<100”)

Figure 12: Query to put multiple conditions in WHERE clause

• The output of the above query will be :

ORDER BY clause for Sorting in ascending order

• If you want to sort the query based on any rule, use the following query :

=QUERY(Dataset, “Select * WHERE D>10 ORDER BY C ASC”)

Figure 14: Query to sort dataset by column C in ascending order

• This is SQL equivalent to Select * from Dataset WHERE D>10 ORDER BY C ASC
• The output of the query will be :

ORDER BY clause to sort by multiple columns

• If you want to sort the query by ascending order of one column and then by descending order of another column, use this query :

=QUERY(Dataset, “Select * WHERE D>10 ORDER BY C ASC, F DESC”)

Figure 16: Query to sort dataset by ascending order of one column and descending order of another column.

• This will first sort the query by column C in ascending order.
• If two values of column C are the same in value, it will then sort by column F in descending order.
• The output is shown below :

LIMIT clause in Google Sheets Query function

• If you want to limit the number of rows you want to see, you can use the LIMIT function.
• The query will be as follows :

=QUERY(Dataset, Select * WHERE D>10 ORDER BY F DESC LIMIT 20″)

Figure 18: Query to limit the number of rows on display

• The above query limits the number of rows to 20. You can adjust the number at your convenience using the LIMIT function.
• The output will be :

MIN, MAX and AVG functions of Google Sheets Query

• If you want to select minimum, maximum or average values of any column, use :

=QUERY(Dataset, “Select MIN(B), MAX(G), AVG(H)”)

Figure 20: Query to select minimum, maximum and average values of columns

• The MIN function helps in selecting the minimum value of column B.
• The MAX function helps in selecting the maximum value of column G
• The AVG function helps in selecting the average value of column H
• The output will be :

COUNT function in Google Sheet Query

• To count the number of occurrences of a particular value in a column, use :

=QUERY(Dataset, “Select COUNT(A), AVG(C) WHERE B>120”)

Figure 22: Query to count the number of occurrences of values in a column 

• The query will count occurrences of values in column A and the average of column C.
• The output will be :

PIVOT function in Google Sheets Query

• To pivot the table about a column, use :

=QUERY(Dataset, “Select AVG(A), AVG(C) pivot B”)

Figure 24: Pivots the query about column B

• The output of the following query will be :

OR function in Google Sheets Query

• To apply multiple conditions in the WHERE clause, use OR command as :

=QUERY(Dataset, “Select * WHERE A=5 OR B=100”)

Figure 26: Applying multiple conditions in WHERE clause

• The query will select all the values of the dataset whenever the value of A=5 or B=100.
• The output will be :

NOT EQUALS clause in Google Sheets Query

• If you want to select data where the value should not be equal to a specific value, use :

=QUERY(Dataset, “Select * WHERE A!=0 AND B=100”)

Figure 28: Query to select data where A is NOT EQUAL to 0.

• This query will select data whenever A is NOT EQUAL to 0 and value of B=100, as shown below :

LABEL clause in Google Sheets Query

• If you want to label a particular column with a different name, use :

=QUERY(Dataset, “Select A, count(A) WHERE B>90 GROUP BY A LABEL COUNT(A) ‘total’ “)

Figure 30: Query to LABEL a new column as total

• They will label the new column, count(A) as total :

OFFSET clause in Google Sheets Query

• The offset clause is used to skip a given number of first rows. Use :

=QUERY(Dataset, “Select * WHERE C<90 AND D>100 OFFSET 250” )

Figure 32: Query to skip first 250 rows of the output using OFFSET clause

• This command will skip the first 250 rows of the query output and then display the remaining rows as :

FORMAT clause in Google Sheets Query

• The format clause is used to specify a formatted value for cells in one or more columns. 
• For example, If you want to keep a specific number of decimal places, use :

=QUERY(Dataset, “Select * FORMAT I ‘0.0’ “)

Figure 34: Using FORMAT clause to limit the number of decimal places

• This will format the specified column to one decimal place, like :

See Also

Top 10 Digital Marketing Add-ons for Google Sheets: Learn about the best digital marketing add-ons in Google Sheets

Google Sheets: How to Use Pivot Tables: Learn how to use Pivot Tables