Google Sheets Query Function

Introduction

In Google Sheets Query function helps us to utilize small subset of SQL language i.e., simple querying of single table. It is based on the google visualization language. It does not have access to joins, window functions etc. But with the constrained feature set itself we can accomplish some amazing results. The affordances provided by the Query function are similar to Pivot Tables but we get some amazing capabilities using this

Simple

Let’s write a simple Query to understand Query Function

query(, , number_of_rows_as_header)

Naming things in google sheets

Headers

Let’s come from last option, headers which specifies that number of rows are headers, this is an optional parameter that needs to be provided in the formulae, sometimes in the dataset we might have one row or more than one row as headers Google sheets tries to automatically figure out how many rows belong to header based on it’s heuristics but sometimes we need to give it how many rows are headers

Formatting Column Outputs

## Labels Labels helps us to change the headers of the retrieved columns, It's similar to aliasing(AS) in SQL, In general labels come at the last of the query statement.

Order By

Order By is used to format the output in way such that the data is data is formatted in an ascending or descending order based on the give row or rows, we can give multiple rows to order by the order in which the rows given takes precedence when ordering the data

## Limit & Offset Limit helps by specifying the number of records that needs to be fetched

Offset helps by offsetting the result by required number of rows, In general Order By, Limit and Offset are used along with each other therefore I made example of Limit and offset in Sheet

## Where Where clause helps us to filter the data based on the condition provided in the where clause, conditions take all comparison operators equals(=),less than (<), greater than (>),not equal to (<>), less than or equal to (<=), greater than or equal to (>=) , we can use logical operators such as AND and OR to chain multiple conditions ### AND AND only satisfies if the condition 1 and condition 2 both are true ### OR OR satisfies if any one of the conditions are true

Cell Reference and Functions inside Select Query Function

we can use functions inside the where clause to better use the where clause for this we need to wrap the references in ‘“&&”‘ anything inside is relative referenced but everything else in SELECT Statement is a string and not relative referenced.

Group By

Group By is used to aggregate data of one column against one or more columns

Grouping using multiple columns

Pivot

Pivot takes 2 columns similar to Group by it needs to aggregate a column based on other column and the data is formatted in row format instead of columns

Links & References

1. Example Doc