QUERY function for Sheets

7 min read

I. Intro

The QUERY function, which uses SQL-like syntax, is a versatile tool for searching and filtering data, with support for aggregate functions.

II. About the function

Formula: QUERY(range, [query], [headers])

Arguments:

Range: The range of data to be queried. Each column can only contain one type of data, which must be a boolean, number (including date/time types), or string.

Query: Optional. When this is omitted, the function will return all values within the range. The query clause should be written in SQL-like syntax and needs to be enclosed in double quotation marks. You can combine clauses led by different keywords, and the clauses need to be separated by spaces. In clauses led by a keyword, you can use aggregate functions, date functions, and operators. A comma should go before the function.

Note: The following keywords are supported: Select, where, group by, pivot, order by, limit, offset, label. If multiple keywords are used, they must follow the order listed. For example, select must be at the beginning, and so on.

Headers (optional): The number of rows to be used as the header. When this is omitted, it is automatically set by the function. Enter 1 to set the first row in the source data as the query header; enter 2 to set the first two rows as headers, and so on.

In this example, the total sales volume for each product needs to be summarized. To try it out yourself, use this template: Use the Query Function (Template).

Formula	Query range	Query result
=QUERY(B2:C10, "select A, sum(B) where A='Item A' group by A" )	250px\|700px\|reset	250px\|700px\|reset

III. Query description

Keywords

Category	Keyword	Description	Example
Query function	select	Specify the columns to be returned, separated by commas. Only column numbers such as A, B, C are supported, not the headers of spreadsheets, such as sales and name. After 'select', you can directly specify the column, or specify the aggregate function of the column, date function, and simple binary operation result. If this instruction is omitted, all columns in the query range will be returned by default.	select D: Select column D select max(D): Select the maximum value in column D select year(D): Select the year in column D select D+E: Select the information in columns D and E
		where	Set the conditions that the result needs to meet (equivalent to filtering). Supported symbols include =, <, <=, >, >=, != and <>, as well as null values, 'null' and 'is not null'. Conditions can be linked through 'and' and 'OR', and parentheses () can be used to distinguish condition priorities. Aggregate functions cannot be used as conditions. When the condition value is text, it needs to be enclosed in single quotes ('). The condition value can reference the value of other cells. If the referenced cell value is of numeric type, it needs to be referenced in the format of "&cell&". If the referenced cell value is of text type, an additional pair of single quotation marks needs to be added on both sides of the reference value, that is, '"&cell&"'.	where D = 35: Filter the information where the value in column D is 35 where D != 'IT' and C > 100: Filter out the information where the value in column D is not equal to IT and the value in column C is greater than 100 where (D='IT' and C > 200) OR (D='IT' and C < 10): Filter out the information where the value in column D is equal to IT and the value in column C is greater than 200, or filter out the information where the value in column D is equal to IT and the value in column C is less than 10 where C="&A1&": Filter out information where the value in column C equals the value in A1 (here, the value of A1 is numerical) where C='"&B1&"': Filter out information where the value in column C equals the value in B1 (here, the value of B1 is text type)
		group by	Sets the aggregate dimension for grouping items of the same category. When the select function contains an aggregation function, it needs to be used in conjunction with group by. The basis for grouping generally includes the original field and the date function field.	select D, max(A) group by D: Select all information in column D and the Maxpoint in column A, and sort by column D select D, year(C), max (A) group by D, year (C): Select all information in column D, the year information in column C, and the Maxpoint in column A, and sort by the values in column D and the year in column C
		order by	Set the basis for sorting, the column used for sorting must be referenced by the select function. It is sorted in ascending order by default. If desc is added after the function, it will be in descending order. Aggregation functions can be used as conditions.	order by A: Sort by the information in column A order by sum(A) desc: Sort by the sum of column A in descending order
		pivot	Convert the unique values in the column into new columns. When using pivot, the select function needs to contain an aggregation function. The column in pivot cannot appear in the select, order by, or group by function. Aggregation functions cannot be used as conditions.	select A,sum(C) group by A pivot B: Select the information in column A, the sum information in column C, aggregate by column A, and convert the unique values in column B into new columns
		like	Supports the following two wildcards for fuzzy text search: %: Matches 0 or more characters of any type. _: Matches 1 character of any type. Can only be used in where, with the same priority as logical comparison operators (such as and, or).	where B like '%S: Filters out text where the last character is 'S' where B like '_S': Filters out text that only contains two characters and the second character is 'S'
		limit	Limits the number of rows returned, only returning the first n rows. When n >= total number of rows in the source spreadsheet, or when n < 0, content from all rows will be returned.	select B limit 5: Only return the first 5 rows of column B
		offset	Skips the first n rows when returning results. Only skips data rows, the header row is not affected. When n >= total number of rows in the source spreadsheet, or when n < 0, no content will be returned.	select B offset 3: Skip the first 3 lines of the return result
		label	Sets the header for 1 or more columns in the spreadsheet. The condition value can be a list header, or the result of an aggregate function, date function, or operator. The column to be modified in the header must be referenced by the select function. Cannot use headers instead of column numbers in the label statement. If the pivot function has been used, the existing header of the column generated by the pivot will become part of the new header.	select A, B label A 'Province', B 'City': Renames column A as 'Province', column B as 'City'

Aggregate and date functions

Type	Function	Description	Example
Aggregate function	avg()	Calculate the average; data types supported include number.	avg(D): Calculate the average of column D
		count()	Calculate the number of non-empty values; data types supported include text, number, and date.	count(D): Calculate the number of non-empty values in column D
		max()	Return the maximum value; data types supported include number and date.	max(D): Return the maximum value in column D
		min()	Return the minimum value; data types supported include number and date.	min(D): Return the minimum value in column D
		sum()	Calculate the sum; data types supported include number.	sum(D): Return the sum of column D
Date function	year()	Return the year contained in the date and time value.	year(D): Return the year contained in column D
		month()	Return the month contained in the date and time value.	month(D): Return the month contained in column D
		day()	Return the day contained in the date and time value.	day(D): Return the day contained in column D

Note: The columns referenced in the date function need to be in time format. For more details, see Change the number format in Sheets.

Operators

Operator	Description	Example
+	Plus sign; only supports the addition of numerical values.	select A+B: Select the sum of values in column A and column B
-	Minus sign; only supports the subtraction of numerical values.	select A-B: Select the difference between the values in column A and column B
*	Multiplication sign; only supports the multiplication of numerical values.	select A*B: Select the product of values in column A and column B
/	Division sign, only supports the division of numerical values. When the denominator is 0, it returns a null value.	select A/B: Select the quotient of values in column A and column B
and	And	where A!='7' and A!='8': Filter out values in column A that are not equal to 7 and 8
or	Or	where A='7' or A='8': Filter out values in column A that are equal to 7 or 8
<> or !=	Not equal to	where B!='7': Filter out values in column B that are not equal to 7
=	Equal to	where B='7': Filter out values in column B that are equal to 7
<	Less than	where B<'7': Filter out values in column B that are less than 7
<=	Less than or equal to	where B<='7': Filter out values in column B that are less than or equal to 7
>	Greater than	where B>'7': Filter out values in column B that are greater than 7
>=	Greater than or equal to	where B>='7': Filter out values in column B that are greater than or equal to 7
is null	Is null	where B is null': Filter out null values in column B
is not null	Is not null	where B is not null': Filter out non-null values in column B

IV. Steps

Use Query function

Open the spreadsheet, select a cell, and enter =QUERY(range, [query], [headers]).

Press Enter.

For example, you can use the forumla =QUERY(A:D, "select B,year(A),sum(D) where B='Southeast' and C='Latte' group by B, year(A) order by sum(D) DESC" to find the total sales of latte in Southeast over three years in the chart below. At the same time, you can group data by store and year, and sort in descending order by total sales. Full details of the formula below:

select: Use the select function to choose the columns you want to display in the query results. In this case, we use:

B: Column B, the column that contains the region.

year(A): The year information from column A.

sum(D): The total of the relevant data in column D (The total sales of the selected region in the selected year)

where: Set the query conditions with the where function, filtering records that meet the query conditions. In this case, we use:

B='Southeast': The value in column B needs to be 'Southeast', where the text value 'Southeast' is enclosed in single quotes.

and C = 'Latte': At the same time, the value in column C needs to be 'Latte', where the text value 'Latte' is enclosed in single quotes.

group by: Set the grouping dimension and sorting method. In this case, we use:

B: Group data by store

year(A): Group data by year

order by: Set the sorting method. In this case, we use:

sum(D): Sort by total sales

desc: Sort in descending order

250px|700px|reset

Delete Query function

Select the cell with the function and press Delete.

V. FAQs

Are query clauses case-sensitive?

No, writing select or SELECT would both work, but column references (column letters) must be capitalized. For example, you must write "select D" instead of "select d".

Written by: Lark Help Center

Updated on 2024-11-21

How satisfied are you with this content?

Thank you for your feedback!

Need more help? Please contact Support.

Overview

ContactSales