How to Use QUERY Function in Google Sheets (User Guide)

how to Use QUERY Function in Google Sheets 14

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.

See also  How to Make Box and Whisker Plot in Google Sheets (Best Practice)

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
See also  How to Use IMPORTRANGE Function in Google Sheets (Complete Guide)

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

how to Use QUERY Function in Google Sheets 1

Step 2 (Optional)

Make your dataset a “Named range”.

2.1 Select the data range

how to Use QUERY Function in Google Sheets 2

2.2 Go to Data Named Ranges

how to Use QUERY Function in Google Sheets 3

2.3 Give a name and click Done.

how to Use QUERY Function in Google Sheets 4

Step 3

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

how to Use QUERY Function in Google Sheets 5

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)

how to Use QUERY Function in Google Sheets 6

Step 5

Pass the second parameter (the SELECT clause)

how to Use QUERY Function in Google Sheets 7

Step 6

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

how to Use QUERY Function in Google Sheets 8

Step 7

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

how to Use QUERY Function in Google Sheets 9

Step 8

Hit Enter and column A will be fetched.

how to Use QUERY Function in Google Sheets 10

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

See also  How to Apply Google Sheets Conditional Formatting If Cell Contains Formula

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.

how to Use QUERY Function in Google Sheets 11

how to Use QUERY Function in Google Sheets 12

See also  How to Jump to Specific Cell or Range in Google Sheets

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.

how to Use QUERY Function in Google Sheets 13

how to Use QUERY Function in Google Sheets 14

See also  How to Indent in Google Sheets [4 Methods]

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

how to Use QUERY Function in Google Sheets 15

Step 2

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

how to Use QUERY Function in Google Sheets 16

Step 3

Add SELECT * then write WHERE clause

how to Use QUERY Function in Google Sheets 17

Step 4

Write your column header (I have column D)

how to Use QUERY Function in Google Sheets 18

Step 5

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

how to Use QUERY Function in Google Sheets 19

Step 6

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

how to Use QUERY Function in Google Sheets 20

Step 7

1, and finished

how to Use QUERY Function in Google Sheets 21

Step 8

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

how to Use QUERY Function in Google Sheets 22

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.

how to Use QUERY Function in Google Sheets 23

how to Use QUERY Function in Google Sheets 24

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”.

how to Use QUERY Function in Google Sheets 25

how to Use QUERY Function in Google Sheets 26

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.

how to Use QUERY Function in Google Sheets 27

how to Use QUERY Function in Google Sheets 28

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.

how to Use QUERY Function in Google Sheets 29

how to Use QUERY Function in Google Sheets 30

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

how to Use QUERY Function in Google Sheets 31

how to Use QUERY Function in Google Sheets 32

how to Use QUERY Function in Google Sheets 33

how to Use QUERY Function in Google Sheets 34

See also  How to Change a Drop down List in Google Sheets

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.

how to Use QUERY Function in Google Sheets 35

how to Use QUERY Function in Google Sheets 36

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.

how to Use QUERY Function in Google Sheets 37

how to Use QUERY Function in Google Sheets 38

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)

See also  How to Find Slope on Google Sheets (3 Methods)

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general