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(
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 fetchedOffset 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 trueCell 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 ‘“&
Group By
Group By is used to aggregate data of one column against one or more columns
Grouping using multiple columnsPivot
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