← Google Sheets from Scratch: Formulas, QUERY, and Collaboration
Lesson
Lesson 1: QUERY: SQL-like select / where / group by queries
Extract, filter, and group data with a single QUERY formula and its easy-to-read query language.
What QUERY is and how it works
What QUERY is and how it works
QUERY is the most powerful function in Google Sheets. It replaces several formulas at once: filtering, sorting, grouping, and aggregation, all in one line. Syntax: =QUERY(range, "query", headers). The first argument is the data range, for example A1:C100. The second is the query string, written in the Google Visualization API query language, which is similar to SQL. The third is the number of header rows (usually 1).
The query is written in double quotes, in English. The order of clauses is strict and can’t change: SELECT → WHERE → GROUP BY → ORDER BY → LIMIT. Examples: SELECT A, B selects the first two columns; WHERE C > 100 keeps only the rows where the value in column C is greater than 100. Text values in a WHERE condition go in single quotes: WHERE A = 'Moscow'.
For grouping with aggregation: SELECT B, SUM(C) GROUP BY B adds up the values in column C for each group in column B. A full example: =QUERY(A1:C11, "SELECT B, SUM(C) GROUP BY B", 1). For simple tasks this replaces a pivot table, and it updates automatically when the data changes.
Lesson notes
What QUERY is and how it works
QUERY is the most powerful function in Google Sheets. It replaces several formulas at once: filtering, sorting, grouping, and aggregation, all in one line. Syntax: =QUERY(range, "query", headers). The first argument is the data range, for example A1:C100. The second is the query string, written in the Google Visualization API query language, which is similar to SQL. The third is the number of header rows (usually 1).
The query is written in double quotes, in English. The order of clauses is strict and can’t change: SELECT → WHERE → GROUP BY → ORDER BY → LIMIT. Examples: SELECT A, B selects the first two columns; WHERE C > 100 keeps only the rows where the value in column C is greater than 100. Text values in a WHERE condition go in single quotes: WHERE A = 'Moscow'.
For grouping with aggregation: SELECT B, SUM(C) GROUP BY B adds up the values in column C for each group in column B. A full example: =QUERY(A1:C11, "SELECT B, SUM(C) GROUP BY B", 1). For simple tasks this replaces a pivot table, and it updates automatically when the data changes.