QUERY FUNCTION (Google Sheets)

The Query function is both an extremely versatile and powerful tool to manipulate your data in Google Sheets. In many ways it works similar to a Pivot table.

There are 6 commands that will be extremely useful to anyone who use this function. These are:

1. SELECT, which allows you to pick the columns of interest.
2. WHERE, which allows you to filter the data in the picked columns.
3. GROUP BY, which allows you to group the data together based on a common feature.
4. ORDER BY, which allows you to rank in alphabetical or numerical order.
5. LIMIT, which allows you to only display the first n results, where n can be any integer.
6. LABEL, which allows you to assign custom headings to any of your columns.

It is important to note that once you have selected the data of interest as the first input in the QUERY function, you then need to use the commands in the order stated above, for them to work. In other words, you can’t use the WHERE command before the SELECT command.

In addition, we can also use normal functions like counts, sums, and averages to further manipulate the data.

I’ll use my trusty Gen 1 Pokémon stats to show you an example of how they work. Let’s say I want to rank the top 10 Pokémon types with the highest average total status points. For simplicity I’ll remove the other stats we’re not interested in.

I’ll start by writing equals QUERY and selecting the “raw” data I want to manipulate. Then, I’ll write SELECT columns C and avg(D) since we are interested in the Pokémon type and average of the total stats. I’ll also add GROUP BY column C as we’re not just interested in the average total stat but rather the average total stat of each Pokémon type. Then by also adding ORDER BY avg(D) DESC and LIMIT 10, only the top 10 Pokémon types with the highest average stats will be displayed. Finally, by writing LABEL avg(D) 'Average’ we can change the label of the avg Total column to just a nicer looking Average.

Oh, and remember that all of the commands have to be inside of one big double quotation marks.