Google Sheet Query Function – All Variations

Google Sheet Query Function – All Variations
Reading Time: 7 minutes
Use of Query function

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.
Figure 1:- Selection of dataset to create a named range
  1. Going to Named Ranges :
  • Go to Data
  • Select Named Ranges
Figure 2: Going to Named Ranges to name our data range
  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.
Figure 3: Naming the data range for the ease of querying

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 :
Figure 5: Entire dataset as the output

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 :
Figure 7: Only specified column displayed

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 :
Figure 9: Multiple specific columns displayed

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 :
Figure 11: Example of usage of WHERE clause in Google Sheets Query function

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 :
Figure 13: Example of usage of WHERE clause for multiple conditions

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 :
Figure 15: Example of sorting query in ascending order

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 :
Figure 17: Example of sorting using multiple columns

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 :
Figure 19: Only 20 rows are displayed for the query output using the LIMIT function

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 :
Figure 21: Example of using MIN, MAX and AVG function

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 :
Figure 23: Example of COUNT function in Google Sheets Query

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 :
Figure 25: Example of PIVOT command in Google Sheets Query

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 :
Figure 27: Example of OR function using Google Sheets Query

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 :
Figure 29: Example of NOT EQUALS function

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 :
Figure 31: Example of LABEL function 

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 :
Figure 33: Example of OFFSET clause in Google Sheets Query

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 :
Figure 35: Example of FORMAT clause in Google Sheets Query

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

One response to “Google Sheet Query Function – All Variations”

Leave a Reply