The QUERY function is used to filter and extract specific information from your data.
- Key Clauses: It supports clauses like SELECT, WHERE, GROUP BY, ORDER BY, PIVOT, LIMIT, and more.
- Basic Syntax: The function syntax is =QUERY(data, query, headers), where data is the data range, query defines the operation, and headers specify header rows (optional).
- Data Manipulation: You can use aggregate functions (e.g., COUNT, SUM), arithmetic operators, and scalar functions with the QUERY function.
- Data Retrieval: Use the SELECT clause to retrieve specific columns or use “SELECT *” to get all columns.
- Filtering Data: The WHERE clause allows you to filter rows based on conditions (e.g., WHERE A = ‘Supervisor’).
- Sorting Data: ORDER BY sorts data based on specified columns and orders.
- Grouping Data: The GROUP BY clause is used with aggregate functions to create pivot-table-like summaries.
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.
Table of Contents
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.
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 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 clause aggregates the values by data table or across the rows.
Order By clause sets the order by rows, it performs the simple sorting
PIVOT clause transforms the unique values in the columns into new columns.
The Limit clause as the name suggests limits the number of rows to be returned.
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)
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 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.
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 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.
=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 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.
Now start writing the QUERY Function in a cell where you want to retrieve the data from the dataset
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)
Pass the second parameter (the SELECT clause)
with a space, pass the third parameter (the column header)
Pass the count of rows that are headers (i have 1 in the example)
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
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)
Select a cell where you want to fetch the data
Write the formula, specify your dataset name (or range),
Add SELECT * then write WHERE clause
Write your column header (I have column D)
Write the condition, I am checking equality (so I used = sign)
In single quotes ‘String-value’ (i have Supervisor as my String-value)
1, and finished
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.
=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”.
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.
=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.
=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.
=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
Sheet! is the sheet name and A: F is the range and the rest you guys know 😉
Download/Copy Google Sheets Workbook
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?
Yes, the google sheets filter function can provide similar results as the QUERY function. By using the filter function, you can specify criteria to include or exclude data within a range. While the QUERY function offers more advanced functionalities, the filter function is simpler and more user-friendly for basic filtering needs. Both functions are useful for data analysis and manipulation in Google Sheets.
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.