Query function in Google Sheets
Hello friends!! Today we’ll be learning about the “Query” function in Google sheets. Query is one of powerful function in Google sheets. Combining with other functions makes it super helpful for the analysis.
QUERY Function Syntax
=QUERY(data, query, [headers])
| Clause | Usage |
| select | Selects which columns to return, and in what order. If omitted, all of the table’s columns are returned, in their default order. |
| where | Returns only rows that match a condition. If omitted, all rows are returned. |
| group by | Aggregates values across rows. |
| pivot | Transforms distinct values in columns into new columns. |
| order by | Sorts rows by values in columns. |
| limit | Limits the number of returned rows. |
| offset | Skips a given number of first rows. |
| label | Sets column labels. |
| format | Formats the values in certain columns using given formatting patterns. |
| options | Sets additional options. |
| from | The from clause has been eliminated from the language. |
Select, Where and Limit Command
=QUERY(Data!A:E,"SELECT A,B,D WHERE B='Male' LIMIT 5",1)
Order by Command
=QUERY(Data!A:E,"SELECT A,B,C ORDER BY B DESC",1)
Group by Command
=QUERY(Data!A1:E31,"SELECT B, COUNT(B) GROUP BY B ",1)
Pivot Command
=QUERY(Data!A1:E31,"SELECT COUNT(C) PIVOT B ",1)