Syntax
=QUERY(data, query, [headers])Arguments
| Argument | Required | Description |
|---|---|---|
data | Required | The range or array to query |
query | Required | The query text, for example "select A, sum(B) where C > 10 group by A" |
headers | Optional | Number of header rows (default: guessed) |
How to use QUERY
When QUERY returns more than one value, the result spills into the cells below and to the right. Keep those cells empty, or you get #SPILL!.
QUERY takes 2 required arguments and 1 optional argument. Type =QUERY( in a cell and SheetsBat shows the arguments as you go. Optional arguments are shown in square brackets; leave them out to use their default.
Examples
Each result below was calculated by the SheetsBat formula engine.
=QUERY({"Name","Score";"Ann",5;"Bob",9;"Cy",7}, "select Col1 where Col2 > 6 order by Col2 desc", 1)Result Spilled result
| A | |
|---|---|
| 1 | Name |
| 2 | Bob |
| 3 | Cy |
=QUERY({"a",1;"b",2;"a",3}, "select Col1, sum(Col2) group by Col1 label sum(Col2) 'Total'", 0)Result Spilled result
| A | B | |
|---|---|---|
| 1 | Total | |
| 2 | a | 4 |
| 3 | b | 2 |
Related array functions
- MAKEARRAYBuilds an array of the given size by calling a LAMBDA with each row and column number.
- MAPApplies a LAMBDA to each value of one or more arrays and returns the results.
- PERCENTOFReturns the sum of a subset of values as a fraction of the sum of all values.
- PIVOTBYSummarizes data by row and column fields and aggregates the values at each intersection.
- REDUCEReduces an array to one value by applying a LAMBDA to an accumulator and each value.
- SCANApplies a LAMBDA to each value with a running accumulator and returns every intermediate result.
- SEQUENCEReturns a list of sequential numbers in an array of the given size.
- SORTSorts the rows or columns of a range or array.
- SORTBYSorts a range or array by the values of other ranges or arrays.