Google Sheets QUERY is one of the most powerful functions in the GSuite universe. It is incredibly versatile and can be applied to both simple and complex problems.
In this ultimate guide, I will walk you through the Google Sheets QUERY function, where to use it, its basic syntax, and much more. Don’t worry, we’ll take it step by step, starting with simple examples and progressing to more complex ones.
What is Google Sheets QUERY?
The Google Sheets QUERY function helps you apply a query to a data table in Google Sheets. For example, I often use it to extract subsets of data from larger datasets. This allows me to analyze specific areas of interest and gain a better analytical understanding.
A query can be thought of as a filter, a pivot table, and much more. If you have worked with databases using SQL, you will find that the format of the Google Sheets QUERY function is quite similar. In fact, the Google Sheets QUERY function mainly involves using SQL-style queries to apply them to a given dataset.
Syntax of the Google Sheets QUERY function
The syntax of the QUERY function is as follows:
=QUERY(data, query_string, [headers])
- Data: The range of cells containing your data.
- Query_string: Contains the query you want to execute on your data.
- Headers: The number of header rows above your data set. This parameter is optional.
The QUERY function reads the query provided in query_string, applies it to the given data, and returns the resulting table.
Note: The query_string contains nothing but a query.
Interestingly, the query format in Google Sheets is similar to that of a normal SQL query. A query usually consists of one or more clauses and may also contain optional aggregation functions and/or arithmetic operations.
Clauses and QUERY
A clause is a part of a query that allows you to filter the provided data. Some clauses allow you to customize how your data will be queried.
For example, the SELECT clause allows you to select a subset of columns from your data set. The WHERE clause in Google Sheets queries complements the SELECT clause by filtering the selected columns based on a condition.
Here are the possible query clauses for your data. Note that they must be used in this order:
- Select
- Where
- Group by
- Pivot
- Order By
- Limit
- Offset
- Label
- Format
- Options
Remember that you cannot specify “group by” before “where” or “select”. The above order is the order you will need to use with the Query function.
Aggregation Functions and QUERY
Aggregation functions are functions that perform a calculation on values. These functions typically return a single value after performing the calculation. Here are some examples of aggregation functions:
- SUM: Adds up all values in a specific column (or subset of column).
- COUNT: Counts the number of rows in a given column (or subset of column).
- AVG: Calculates the average of values in a given column (or subset of column).
- MIN: Finds the lowest value in a given column (or subset of column).
- MAX: Finds the highest value in a given column (or subset of column).
Aggregation functions are commonly used in conjunction with the GROUP BY clause (which we will explore a bit later in this tutorial).
Arithmetic Operations and QUERY
Arithmetic operations are expressions composed of a constant, a variable or a scalar function, as well as operators such as addition (+), subtraction (-), multiplication (*), division (/), or modulo (%). They can also include comparison operators such as >, <, =, <=, >=.
These operators are used to perform mathematical operations on selected data from your main data set.
10 Examples of Google Sheets Queries
To show you how to use the Google Sheets QUERY function, we will apply different queries to the following sample data:
1. Simple query using the SELECT function
Let’s start with a simple query formula in Google Sheets. We want to select and display a single column from the data.
=QUERY(A:F, "SELECT A", 1)
The formula above applies the query “SELECT A” to the data table A:F.
In SQL, this formula would be equivalent to the query “SELECT Name FROM Dataset” (if the referenced columns A:F were named Dataset).
The SELECT clause tells the function which columns from the main data set to consider when extracting the data. The query expression is written in the Google Visualization API query language (which is quite similar to query expressions used in SQL).
Here’s the result that the above formula returns:
The formula has returned the ‘Name’ column from the dataset as it refers to column A.
Similarly, if you only want to see the ‘Address’ column, then your QUERY formula would be:
=QUERY(A:F, "SELECT D", 1)
Using Named Ranges in the QUERY function
You can also use named ranges in your query. For example, you can give the data range A:F the name ‘Dataset’ by selecting columns A to F and typing the name ‘Dataset’ in the ‘Name’ box (see below), then pressing Enter:
You can then use the range name in the QUERY function as follows:
=QUERY(Dataset, "SELECT D", 1)
Note: The Google Sheets QUERY formula returns only the data table, but without the formatting.
Access the sample sheet by clicking on the following link: QUERY Workbook
2. Using the Google Sheets QUERY function to select multiple columns
You can also use the SELECT clause to display more than one column. For example, if you want to display both the ‘Name’ and ‘Address’ columns, your QUERY formula would look like this:
=QUERY(Dataset, "SELECT A, D", 1)
The asterisk (*) symbol can be used in your SELECT clause to display all columns. For example, you can use the SELECT clause to display all columns of your data set like this:
=QUERY(Dataset, "SELECT *", 1)
3. Filter and extract data using SELECT with the WHERE clause
If you want to specify certain conditions that a row must meet to be displayed in a query, you can add a WHERE clause to your query like this:
=QUERY(Dataset, "SELECT * WHERE B='Manufacturing'", 1)
When added to the SELECT clause, the WHERE clause lets you filter the returned data using one or more conditions. For example, if you only want to extract data for employees in the ‘Manufacturing’ department, use the QUERY function like this:
=QUERY(Dataset, "SELECT * WHERE B='Manufacturing'", 1)
You can also use “comparison operators” in the WHERE clause. For example, to extract data for employees who have worked more than 30 hours, you can write a query like this:
=QUERY(Dataset, "SELECT * WHERE E>30", 1)
You can also use the QUERY function with wildcard characters if you want the conditions to be characters rather than whole words.
Using SELECT WHERE with AND and OR
Add multiple search criteria to your query using logical operators such as OR and AND. For example, you can expand the search from the above formula to include all employees from both ‘NY’ and ‘NJ’ like this:
=QUERY(Dataset, "SELECT * WHERE D CONTAINS 'NJ' OR D CONTAINS 'NY'", 1)
Similarly, to include two search criteria that both need to be met for a row to be considered, you can use the AND operator. If you want to display data for employees who live in ‘NJ’ and are from the ‘Manufacturing’ department, you can use the following formula:
=QUERY(Dataset, "SELECT * WHERE D CONTAINS 'NJ' AND B='Manufacturing'", 1)
4. Using the ORDER BY clause of Google Sheets QUERY
The ORDER BY clause is typically used in addition to the SELECT and WHERE clauses. It allows you to sort the selected data in ascending or descending order. You can specify the column by which you want to sort your data, as well as select ascending or descending order.
For example, let’s say you want to display data for all employees in the ‘Manufacturing’ department in alphabetical order by name. You can write your QUERY formula like this:
=QUERY(Dataset, "SELECT * WHERE B='Manufacturing' ORDER BY A ASC", 1)
Similarly, to display the data in descending order of ‘Hours worked’, your QUERY formula would be:
=QUERY(Dataset, "SELECT * WHERE B='Manufacturing' ORDER BY E DESC", 1)
The LIMIT clause
If you have a data set with multiple identical data points, you can use the LIMIT clause to display a certain number of entries. For example:
=QUERY(Dataset, "SELECT * WHERE B='Manufacturing' ORDER BY E DESC LIMIT 5", 1)
5. Using QUERY to group selected data using the GROUP BY clause
The GROUP BY clause is one of the most challenging query concepts to master. The only way to master this clause is through practice. The GROUP BY clause allows you to group your data into groups, similar to a pivot table.
The GROUP BY clause is typically combined with aggregation functions like SUM, MAX, and MIN, in addition to the SELECT and WHERE clauses.
For example, a common application of this function is to count the number of rows matching a given category. Here, data rows are grouped by category, and then the count of rows for each group or category is counted.
Suppose you want to group your data on employees by ‘Department’ to see how many employees are present in your data set for each department. In this case, your query would look like this:
=QUERY(A1:F9, "SELECT B, COUNT(B) GROUP BY B", 1)
Let’s say I want to display the B (Department) column and also group the column by Department. I have also specified that for each department group, I want to display the count of rows (using the COUNT(B) aggregation function).
Similarly, if I want to display the total number of hours worked by employees in each department, I could use the following query:
=QUERY(A1:F9, "SELECT B, SUM(E) GROUP BY B")
Going Further
Thanks to our step-by-step examples, you should now have a better idea of how to use the Google Sheets QUERY function to extract information from a given dataset. I hope this tutorial has been helpful to you.
If you have any questions or comments about the article you just read, leave me a comment!
