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 of query syntax options along with practical examples.
Basic Syntax of QUERY Function
=QUERY(data_range, query, [header_rows])
Example: =QUERY(A1:D10, “SELECT A, B WHERE C > 100”, 1)
This example selects columns A and B from the range A1:D10 where the values in column C are greater than 100.
Query Syntax
SELECT (Column Selection)
| Query |
Description |
SELECT A, B |
Selects columns A and B. |
SELECT * |
Selects all columns. |
WHERE (Conditional Filtering)
| Query |
Description |
WHERE C > 100 |
Gets rows where the values in column C are greater than 100. |
WHERE D = 'Tokyo' |
Gets rows where column D equals “Tokyo.” |
WHERE A CONTAINS 'abc' |
Gets rows where column A contains the string “abc.” |
ORDER BY (Sorting)
| Query |
Description |
ORDER BY B |
Sorts rows in ascending order based on column B. |
ORDER BY C DESC |
Sorts rows in descending order based on column C. |
LIMIT (Restrict Number of Rows)
| Query |
Description |
LIMIT 10 |
Retrieves the top 10 rows. |
LIMIT 1 |
Retrieves only the first row. |
OFFSET (Specify Starting Position)
| Query |
Description |
OFFSET 5 |
Skips the first 5 rows. |
LIMIT 10 OFFSET 5 |
Retrieves 10 rows starting from the 6th row. |
GROUP BY (Grouping)
| Query |
Description |
GROUP BY A |
Groups data by the values in column A. |
SELECT A, SUM(B) GROUP BY A |
Calculates the sum of column B values grouped by column A. |
PIVOT (Create Pivot Table)
| Query |
Description |
PIVOT B |
Pivots the values in column B. |
Usage Example
Here is an example of the QUERY function in action:
|
A |
B |
C |
| 1 |
Item |
Price |
Category |
| 2 |
Apple |
100 |
Fruit |
| 3 |
Orange |
120 |
Fruit |
| 4 |
Cabbage |
80 |
Vegetable |
Query Example:
=QUERY(A1:C4, “SELECT A, B WHERE C = ‘Fruit’ ORDER BY B DESC”, 1)
This query retrieves the names and prices of items in the “Fruit” category, sorted in descending order of price.
