The QUERY function in Google Sheets is a powerful tool that allows you to manipulate and analyze data using SQL-like queries. This function have capabilities to combined the other many function such as FILTER, SORT, VLOOKUP, SUM and much more to perform the calculations of your data, making it easy to generate reports or summaries. Here, we take a "Employees List-Salary" and put lots of effort into this article to turn it into the ultimate beginner tutorial that covers the QUERY function of Google Sheets and the question that have uses in your daily workout in sheets.
What is the QUERY Function?
The QUERY function in Google Sheets lets you extract data based on specific criteria, while also allowing you to modify it. You can use this single function to manage data as filter, sum, vlookup, sort, calculate, and rearrange columns or rows as needed, without changing the original data. This way, your main data stays untouched, and you can work with just the information you need on your sheet.
Syntax:
QUERY(data, query, [headers])
data: The range of cells containing the data you want to apply for the query.query: The SQL-like query string used to manipulate the data, enclosed or start with and end with double quotations.("query string")headers (optional):This parameter is optional but can be helpful when your data has headers. Put, the number of header rows in the data.
The QUERY function takes three arguments. The first and last are straightforward to use, but the second argument often creates difficulties for beginners. This second argument is crucial because its SQL-like queries function which defines how the data is filtered, sorted, or modified, making QUERY unique and more versatile compared to other functions.
It transforms Google Sheets into a powerful tool, enabling more complex data manipulation similar to what you’d find in programming environments.
QUERY Function Arguments in Google Sheets:
- In the below example, the first argument is (
data),"'Emp-salary-list'!B2:H", - The second argument (
query) is"SELECT B, C, D, E"and - The last argument is
[headers]as1. - Remember that all arguments must be separated by commas
( , ).
=QUERY('Emp-salary-list'!B2:H, "SELECT B, C, D, E", 1)
We put formula in cell B2 as shown image below, and the result displayed.
If you want to change in order of column or header data, simply adjust the order in the second argument of QUERY function, as shown below or based on your preference. The differences between the two outputs, as shown in the images above and below, demonstrate how the sequence of columns changes according to the second argument.
If you want to switch columns or reorders:
=QUERY('Emp-salary-list'!B2:H, "SELECT C, D, E, B", 1)
This formula reorders the columns and displays them in the sequence C, D, E, and B. According to the images above and below, the header sequence changes—‘Name’ now appears in the first column, while ‘Employee Code’ moves to the last column instead of the first position, as previously shown. This demonstrates how easily you can customize the layout of your data using the QUERY function in Google Sheets.
Difficulties for Beginners in QUERY Function, Showing errors as #VALUE! in Google Sheets?
The image below showing ” #VALUE! ” an error, which occurs when there’s a mismatch between the first and second arguments in the QUERY function. In this case, the data_range is 'Emp-salary-list'!B2:H, but the second argument specifies "SELECT A, B, C, D, E". Since column A does not exist in the provided data range, the formula results in an error. The correct formula would need to select columns based on the actual columns within the specified range:
=QUERY('Emp-salary-list'!B2:H, "SELECT A, B, C, D, E", 1)
Common Clauses or Key Parts of the QUERY Function:
- SELECT (choose columns): Specifies which columns you want to retrieve. You can select one or multiple columns.
SELECT C, D, E, B - WHERE (filter rows): Adds conditions or filters to the data you retrieve.
Where C is not null and E='Female' - ORDER BY (sort results): Sorts the data based on specified columns.
Order by C ASC - LIMIT (limit): Restricts the number of rows returned.
Limit 10 - LABEL (rename column): Changes the labels of the output columns.
Label C 'Emp-Name'
In the image below, the QUERY function uses common clauses like SELECT, WHERE, ORDER BY, LIMIT and LABEL to extract and organize data from the sheet named ‘Emp-salary-list’. These clauses help filter, sort, reorder, limit and label columns as needed, allowing you to customize the data presentation while keeping the original data unchanged.
=QUERY('Emp-salary-list'!B2:H, "SELECT C, D, E, B Where C is not null and E='Female' Order by C ASC Limit 10 Label C 'Emp-Name'", 1)
Let’s dive deeper into the GROUP BY clause in the QUERY function of Google Sheets. The GROUP BY clause is used to group rows that have the same values in specified columns. It’s often used with aggregate functions to perform calculations on each group. Here’s a Syntax explanation:
Basic Syntax for using the GROUP BY is:
=QUERY(data_range, “SELECT column(s), aggregate_function(column) GROUP BY column(s)“)
=QUERY('Emp-salary-list'!B2:H100, "SELECT D, Sum(F), Sum(G), Sum(H) Where C is not null Group by D", 1)
GROUP BY (same values to group): Aggregates data based on the specified columns (useful for summaries or counts).
- Group all rows with the same rank.
- Sum the basic pay for each employee group.
- Return a list of all ranks with their total basic pay, allowances and gross.
Here as image shown above, data collapse from multiple rows to a single row for each group which calculate each group using aggregate functions. Any column in the SELECT clause that is not an aggregate function must be included in the GROUP BY clause. You cannot use WHERE to filter on aggregated results.
GROUP BY function supports to several aggregate functions: How It Works
GROUP BY typically used with aggregate functions, list as below:
- COUNT(): Counts the number of rows
- SUM(): Adds up values
- MIN(): Finds the minimum value
- AVG(): Calculates the average
- MAX(): Finds the maximum value
=QUERY('Emp-salary-list'!B2:H100, "SELECT D, Count(D), Sum(F), Min(F), Avg(G), Max(H) Where C is not null Group by D",1)
Other Formulas OR Examples, Which May Help You:
=QUERY( QUERY(A2:D, "SELECT B, SUM(C) GROUP BY B LABEL SUM(C) 'Total Sales'"), "SELECT * WHERE Col2 > 1000 ORDER BY Col2 DESC" )
Double QUERY function with aggregate function:
=QUERY( QUERY(A2:D,
"SELECT B, SUM(C) GROUP BY B LABEL SUM(C) 'Total Sales'"),
"SELECT * WHERE Col2 > 1000 ORDER BY Col2 DESC" )
The first query (purple) sums the sales amounts per “Salesperson” while the second query (blue) filters to show only those with a “Total Sales” greater than 1000 and orders them in descending order.
This is a simple way to summarize data by salesperson, showing only those with high sales and ordering them by the amount as shown above.
=QUERY(A2:D, "SELECT D, COUNT(B), SUM(C), AVG(C), MAX(C), MIN(C) Where D is not null GROUP BY D LABEL COUNT(B) 'Product Count', SUM(C) 'Total Sales', AVG(C) 'Average Sale', MAX(C) 'Highest Sale', MIN(C) 'Lowest Sale'" )
- Product Count: Counts the number of salespersons in each region.
- Total Sales: Sums the sales amounts in each region.
- Average Sale: Calculates the average sales amount in each region.
- Highest Sale: Finds the highest sale amount in each region.
- Lowest Sale: Finds the lowest sale amount in each region.
This QUERY generates a region-wise summary with various aggregate metrics, using the GROUP BY clause as shown below:
COUNT(B)SUM(C)AVG(C)MAX(C)MIN(C)GROUP BY DLABEL
This QUERY function is used to summarize data by “Region” (column D) and perform various aggregate calculations like count, sum, average, maximum, and minimum for each region in the dataset.
=QUERY(A2:D, "SELECT A, SUM(C) WHERE YEAR(A) = 2023 and A is not null GROUP BY A PIVOT D")
The result of the QUERY formula would be a pivot table, the result looks something like as image below right side of your:
If you need more advanced pivoting options or instead of trying to use PIVOT directly in the QUERY function, you should create a pivot table using Google Sheets built-in Pivot Table feature, which is specifically designed for such tasks.
- Select your data range.
- Click on Data > Pivot Table.
- Set your rows, columns and values accordingly to achieve the desired result.
Convert PDF Data to Google Sheets with Built-in Formulas
=QUERY(A2:D, "SELECT YEAR(A) , MONTH(A), SUM(C) Where B is not null GROUP BY YEAR(A), MONTH(A) ORDER BY YEAR(A), MONTH(A) LABEL YEAR(A) 'Year', MONTH(A) 'Month', SUM(C) 'Monthly Sales' " ,1)
=QUERY(A2:E, "SELECT E, COUNT(B), SUM(C) GROUP BY E ORDER BY E LABEL E 'First Letter', COUNT(B) 'Product Count', SUM(C) 'Total Sales'")
The formula will summarize the data by the first letter of the values in column B, grouping the results based on that first letter. It also counts the occurrences of products and sums their sales.
The LEFT() function is not directly supported in Google Sheets QUERY language so here handle such tasks like extracting the first letter of a string in a QUERY, we use here a helper column to perform the LEFT() function outside the QUERY (add a new column E “1st Letter” as image shown).
In Google Sheets, several additional clauses can significantly enhance the functionality of the QUERY function. Each of these below clauses enhances how the data is retrieved and presented in your Google Sheets, allowing for greater flexibility and customization.
- Pivot : The Pivot clause transforms distinct column values into new columns, making it easier to analyze data from different perspectives.
=QUERY(A2:C11, "SELECT B, SUM(C) GROUP BY B PIVOT A")This will transform distinct regions (column A) into new columns with the total sales per salesperson.
- Offset : This clause allows you to skip a specified number of initial rows, useful for excluding headers or irrelevant data.
=QUERY(A2:C11, "SELECT * OFFSET 2") - The formula will return all the rows the range A2:C11 but skip the first two rows of the data. The
OFFSET 2part tells the query to start displaying results from the third row onward, essentially ignoring the first two rows.
- Format : The Format clause applies specific formatting patterns to the values in selected columns, ensuring consistency and clarity in presentation
=QUERY(A2:D11, "SELECT A, B, C FORMAT C '$#,##0.00'")
- Options : Finally, the Options clause provides additional customization, making your query output more reliable and suited to your needs.
=QUERY(A2:D11, "SELECT A, B, C OPTIONS no_format")
The OPTIONS clause in a QUERY does not include a “no_format” option to prevent or strip formatting. If you want to display raw data without formatting, you would need to adjust the formatting settings of the cells manually. The QUERY function will retrieve and display the data in its current format but does not control how it is visually formatted.
The TEXT() function can be a solution if you want to control how data is displayed or formatted in Google Sheets. The TEXT() function allows you to apply custom formatting to numbers, dates, or text when using them in formulas, including the QUERY() function.
=QUERY(A2:D11, "SELECT A, B, TEXT(C, '0.00')")
Column C will display numbers formatted to two decimal places.
IMPORTRANGE Function in Google Sheets, Benefits and Drawbacks
ArOperators:
- Comparison:
=, >, <, >=, <=, != - Logical:
AND, OR, NOT - Arithmetic:
+, -, *, /
=QUERY(A2:E, "SELECT A, B, (C + D) WHERE C > 500 AND D <= 1000 OR B != 'John' ORDER BY (C - D) DESC")
Advanced Features
- Pivot: Transforms rows into columns Example: “SELECT A, PIVOT(B)“
- Date functions: Example: “SELECT C, D, E WHERE B >= date ‘2023-01-01′”
- String functions: CONTAINS, STARTS WITH, ENDS WITH Example: “SELECT C, D, E WHERE E CONTAINS ‘Female'”
Advantages:
- Data Filtering: Apply complex filters and criteria without needing additional formulas.
- Dynamic Reports: Generate summaries or filtered views from large datasets.
- Multiple Data Sources: Use multiple ranges or combine datasets using joins.
The QUERY function is highly flexible and ideal for creating dynamic reports and advanced data analysis.
Basic Query to Select Data:
=QUERY(A2:D11, "SELECT A, B, C, D", 1)
This retrieves the first five columns (A, B, C and D) from the data in the range A1.
Filtering Data:
=QUERY(A2:D11, "SELECT A, B, C WHERE C > 800", 1)
This retrieves data from column A, B and C where the value in column C is greater than 800.
Sorting Data:
=QUERY(A2:D11, "SELECT A, B, C ORDER BY B DESC", 1)
This retrieves columns A, B and C and sorts the data by column B in descending order.
Limiting Rows:
=QUERY(A2:D11, "SELECT A, B, C LIMIT 5", 1)
This retrieves only the first 5 rows of data from columns A, B and C.
Summarizing Data:
=QUERY(A2:D11, "SELECT B, COUNT(D) GROUP BY B", 1)
=QUERY(A2:D11, "SELECT A, B, C, D, COUNT(B) GROUP BY B", 1)
The error ( #VALUE! ) is showing here because in a GROUP BY clause all columns selected (except the ones aggregated with functions like COUNT, SUM, etc.) must be included in the GROUP BY. However, in your query, columns A, C and D are not grouped or aggregated.
How to fix this:
- Either you need to include all selected columns (A, C and D) in the GROUP BY clause.
- Or you should apply aggregation functions (like COUNTA, SUM, etc.) to those columns if you don’t want to include them in GROUP BY.
=QUERY(A2:D11, "SELECT B, COUNT(B), MAX(A), MAX(C), MAX(D) GROUP BY B", 1)
This counts the number of occurrences in column A for each unique value in column B.
Tips and Tricks
- Use single quotes for string literals within the query.
- You can nest QUERY functions for more complex operations.
- Combine with other Google Sheets functions for even more powerful analysis.
Limitations
- Cannot reference multiple sheets or external data sources directly.
- Some SQL features like JOIN are not available.
Did you like this article?
If you found this post helpful, feel free to share it with your network using the social media buttons on X or via email located on the left below.
YOU MUST KNOW?
For any questions or assistance with the QUERY function—a powerful, SQL-like tool in Google Sheets—feel free to reach out in the comments. The CountLen team is committed to delivering prompt and effective solutions, ensuring all your queries are addressed swiftly. If you encounter any inaccuracies or misleading information, don’t hesitate to share your feedback in the comments. We’re here to help you make the most of your data!
