How to Use QUERY Function in Google Sheets [User Guide]

In this article, we will learn how to use the QUERY Function in google sheets.

QUERY is a term that is widely used in databases and programming languages like SQL. We have a similar concept of QUERY functions in google sheets. QUERY is an instruction or a set of instructions that has components and clauses. QUERY defines the function to be performed and defines the data (or the part of data) to which the QUERY Function will be applied to. The concept of QUERY is coming from the convention of a structured query language that is now known as SQL. We will learn how to use the QUERY Function in google sheets using the traditional concept of QUERY.

QUERY Function in Google Sheets

We work on complex data in google sheets, we may have several sheets in a workbook and we have a lot of different methods and functions to be run on the data to retrieve the required information in the right manner from a very specific location. When working with large datasets you must be noticed that at the time of retrieving, we have very specific data from a very large dataset that we call for our need. So, this is why we must learn how to use QUERY Function to perform operations and functions on our large data sets while specifying the operation, and data location of our choice.

• To manipulate our data using a defined query
• To describe the data location and the operations to be performed on the data

How to Use QUERY Function in Google Sheets

From this section, we will be learning the basic components of the QUERY Function and also the other function that are considered the advanced features of the QUERY Function.

QUERY Function Clauses

Clauses are the go-to components of any function in general. In programming, clauses are the sub-ordinates of any function that goes with the function and takes real space in the memory allocation. Here, clauses are used to specify the action or what you want to do with the data. The simple SELECT clause (also in SQL) allows you to select the data or a part of the data from the given range, and also the order of the data to be returned. similarly, we have the WHERE clause that is used to specify a condition and the only row will be returned that satisfies the given condition. Let’s see more clauses in the detail.

SELECT

The select clause specifies what data to select from a given range, and what should be the order of data. If we don’t use the Select clause, then all the data will be selected from the given range and it will be in the default order.

WHERE

Where clause returns rows that satisfy the condition specified with the query, if you don’t use the Where clause, then all the rows will be returned with no condition checking.

GROUP BY

Group By clause aggregates the values by data table or across the rows.

ORDER BY

Order By clause sets the order by rows, it performs the simple sorting

PIVOT

PIVOT clause transforms the unique values in the columns into new columns.

LIMIT

The Limit clause as the name suggests limits the number of rows to be returned.

LABEL

Label clause is used to name or label the columns

and some more that are not used commonly.

QUERY Function Syntax

Let’s break down the syntax. We have three parameters that the function accepts three parameters or you can say the arguments:

=QUERY(data, query, headers)

Here,

data –  the reference of the data (data-range)

query – defines the overall function of the query, means what we want to do

headers – header counts in the data (it is an optional argument)

QUERY Functions For Data Manipulation

We have 3 main categories of the function that we use with the query, the functions are for different methods, let’s see how what these functions are, and how they work.

Aggregate Function

Aggregate functions are the functions that perform the basic calculations of the values in the dataset, they are used to perform calculations like finding MAX, MIN, AVERAGE, COUNT, and SUM. These five are called aggregate functions in the SQL as well.

These functions are normally used along with Group by clauses.

Arithmetic Operators

As the name suggests, arithmetic operators are general operators used in day-to-day maths. These operators include addition, subtraction, multiplication, division, and modules. These operators are used along with select clauses.

Scalar Functions

Scalar functions are not used, most of us don’t even know about them. The scalar function simply transforms a value into another similar state of it, which means changing a date (DD-MM-YYY) into a single digit.

A SELECT QUERY to Retrieve Single Column

In this section, we will learn how to use Query Function in Google sheets using a simple QUERY using Select clause. Select simply selects the data or the part of the data and retrieve it. It is not as simple, it can have conditions that help us to get the specific data.

Let’s start with s simple dataset.

Syntax

=QUERY(A:F,”SELECT A”,1)

Here,

=QUERY: is the function name

A:F: is the data range (from column A to column F)

Select: is the clause that selects the given column (header) name

A: the column (header) name you want to fetch

1: to specify that 1 row is the header of your data (if your header has two rows, then you will pass 2.

Having said that, let’s move to our example and learn to perform a basic query using the QUERY Function.

Step 1

Simple dataset

Step 2 (Optional)

Make your dataset a “Named range”.

2.1 Select the data range

2.2 Go to Data Named Ranges

2.3 Give a name and click Done.

Step 3

Now start writing the QUERY Function in a cell where you want to retrieve the data from the dataset

Step 4

Pass the first parameter (data range A1:F10), or if you are using named ranges then pass the range name (i am using named ranges)

Step 5

Pass the second parameter (the SELECT clause)

Step 6

with a space, pass the third parameter (the column header)

Step 7

Pass the count of rows that are headers (i have 1 in the example)

Step 8

Hit Enter and column A will be fetched.

This is how simply you can retrieve column(s) data using a simple SELECT query.

A SELECT QUERY to Retrieve Multiple Columns in Google Sheets

Same method but a little change in the syntax can help you fetch multiple columns

=QUERY(A:F,”SELECT A,B”,1)

Add another column header separated by a comma to retrieve multiple columns, similarly, you can add more columns separated by a comma.

QUERY Function in Google Sheets to Select All

Again, the same method, but we can have a big change by changing the syntax a little bit. We have * an asterisk to select all from a given data set.

=QUERY(Data1,”SELECT * “,1)

As you see, I have removed the column header(s) and added a single * asterisk. It means to select all columns from the dataset given.

Retrieve Filtered Data using SELECT And WHERE

In this section, we will learn how to use the QUERY Function in Google sheets to retrieve specific data using Select and Where clauses. Now, this is something advanced that helps us define the condition based on that condition we can extract the data, for example, select all from column A where the designation is equal to supervisor.

As I have written in English above, we can make similar logic in our queries using Select and Where clauses.

=QUERY(Data1,”SELECT * WHERE D = ‘Supervisor'”,1)

Step 1

Select a cell where you want to fetch the data

Step 2

Write the formula, specify your dataset name (or range),

Step 3

Add SELECT * then write WHERE clause

Step 4

Write your column header (I have column D)

Step 5

Write the condition, I am checking equality (so I used = sign)

Step 6

In single quotes ‘String-value’ (i have Supervisor as my String-value)

Step 7

1, and finished

Step 8

Press Enter and you will get only the rows that have “supervisor” in Column D.

Using Numeric Comparison

This method is also like the above method, but we will see the numeric comparison here. In the previous section, we used a condition where we checked based on equality of Strings, if String values matches, only then return those rows, here we will use the same concept but we will use compare data numerically.

=QUERY(Data1,”SELECT * WHERE E > 4″,1)

In Plain English, we are saying that Select all from the data1 (range) where column E value is greater than 4. Now E column has employee grades, so we will get only those employees’ data in the table that are upper to grade 4.

Using Contain

Here we will see how to search and match strings using contain the keyword. This keyword is very commonly used in JavaScript DOM manipulation. If sees if a cell contains any string or a part of a string (here string can be numbers, or text as well).

=QUERY(Data1,”SELECT * WHERE A contains ‘e'”,1)

In plain English, we are saying that Select all from the data1, where column A (cells) contains the “e”, now it will return only the names (Column A) that contain the letter “e”.

OR Operator

Since you have understood the basic concept, now I am only putting the formulas and showing you practical results

=QUERY(Data1,”SELECT * WHERE A contains ‘e’ or B contains ‘1’” ,1)

OR operator needs to have any condition true to be true if both conditions are true, it is true if both are false it false, and if one is true and one is false then it’s also true.

AND Operator

=QUERY(Data1,”SELECT * WHERE A contains ‘a’ and B contains ‘1’” ,1)

AND the operator needs to have both conditions true to be true, if any condition is false, the entire statement becomes false.

ORDER BY CLAUSE to Sort Data

In this section, we will learn the ORDER BY clause, which sorts the data, the entire query remains the same and the order by clause is used at the end to specify the sorting order and the sorting column.

=QUERY(Data1,”SELECT * WHERE D = ‘Executive’ ORDER BY A DESC”,1)

Here, we want to retrieve the data and at the end, we specify the column (A) and how to sort ascending or descending order

GROUP BY CLAUSE with Aggregate Function to make Pivot Table

In this section, we will learn the GROUP BY clause, it simply groups the data and gives a pivot table feel. We can group the data in different ways.

COUNT

=QUERY(Data1,”SELECT C, count(C) GROUP BY C”,1)

Here we want to pack data by column C (Dept) we will COUNT the depts and will count the number of people associated with this dept.

SUM

=QUERY(Data1,”SELECT C, SUM(F) GROUP BY C”,1)

Here we want to pack data by column F (Hourly Rate) we will SUM the hourly rate of all employees in one dept and will the total hourly rate for each dept.

Query Function on Adjacent Sheets

=QUERY(Sheet1!A:F,”SELECT A,B”,1)

Here,

Sheet! is the sheet name and A: F is the range and the rest you guys know 😉

Download/Copy Google Sheets Workbook

Notes

Query function can also be used for other workbooks along with importrange function Syntax for query function with importrange =QUERY(IMPORTRANGE(sheet_url,sheet_name!cell_range_reference),”SELECT *”,1)

Frequently Asked Questions

Can the IMPORTRANGE function be used in combination with the QUERY function in Google Sheets?

Yes, the IMPORTRANGE function can be used in combination with the QUERY function in Google Sheets. By using the IMPORTANGE function, you can import data from one sheet to another, and then apply the QUERY function to manipulate and filter the imported data further. This powerful combination allows for more advanced data analysis capabilities using importrange function.

Can I Use the Filter Function in Google Sheets to Achieve the Same Results as the QUERY Function?

Conclusion

So, guys, we have learned how to use the QUERY Function in google sheets. We have seen a lot of formulas, concepts, and examples, so I will recommend you to practice and give some time to QUERY Function, once you got the taste of it, you will frequently use this function. I guarantee you this is a powerful function.

So, guys wrapping up here, I hope you enjoyed the tutorial, and you learned a lot from this. Thank you, see you soon again. Keep learning with Office Demy.