About the QUERY Function
Overview of QUERY
| Filter and Manipulate Data Using SQL-like QueriesGoogle Sheets Function | ||
|
=QUERY( data_range, query, [header_rows] ) Summary The QUERY function is a powerful tool for filtering, aggregating, and sorting data within a spreadsheet using SQL-like syntax. |
||
|
When to Use QUERY
- To extract data based on specific conditions.
- When sorting or aggregating data is required.
- For efficient data manipulation using SQL-like syntax.
How to Use QUERY
The following table demonstrates the basic usage of the QUERY function.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Age | Score | |
| 2 | John | 20 | 85 | |
| 3 | Jack | 22 | 90 | |
| 4 | Smith | 19 | 78 |
The examples below show how to use QUERY:
| Description | Formula | Result |
|---|---|---|
| Extract rows with scores of 80 or higher | =QUERY(A1:C4, “SELECT A, C WHERE C >= 80”, 1) | Displays data for John and Jack |
| Sort by age in descending order | =QUERY(A1:C4, “SELECT A, B ORDER BY B DESC”, 1) | Order: Jack, John, Smith |
| Calculate the average score | =QUERY(A1:C4, “SELECT AVG(C)”, 1) | Average score: 89 |
Results
- Query syntax allows easy manipulation of data.
- Data can be filtered and sorted based on specific conditions.
List of Queries Used in the QUERY Function
QUERY Function: Query Syntax and Examples in Google Sheets
The QUERY function in Google Sheets is a powerful tool for filtering, sorting, and aggregating data flexibly within a spreadsheet. This article provides a list ...
programming-cafe.com
Advanced Examples of QUERY
The QUERY function enables complex data processing with ease. Below are advanced use cases:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Age | Score | Region |
| 2 | John | 20 | 85 | New York |
| 3 | Jack | 22 | 90 | Los Angels |
| 4 | Smith | 19 | 78 | Chicago |
| Description | Formula | Result |
|---|---|---|
| Extract top scorers from a specific region | =QUERY(A1:D4, “SELECT A, C WHERE D = ‘New York’ AND C >= 80”, 1) | Displays John only |
| Calculate average scores by region | =QUERY(A1:D4, “SELECT D, AVG(C) GROUP BY D”, 1) | Displays the average scores for each region |
Points to Note
- The data range must include a header row.
- Query strings should be enclosed in single quotes.
- Errors may occur if data types are inconsistent.
Conclusion
- The QUERY function is a powerful tool for efficient filtering, aggregation, and sorting of data.
- SQL-like syntax provides flexibility in data manipulation.
- By paying attention to header rows and data formatting, complex data operations can be simplified.