Tutorial - QUERY: “The most powerful function” in Google Sheets (part 2)

If you have not read part 1, below is the direct link so you can have a read at what QUERY is, and how it can help your case, whether it be building Glide apps or make your normal day work easier.

6/Dynamically sort your data using ORDER BY

An ORDER BY claused can be used to dynamically sort your data after being queried, and you can change the column that is used to sort with just a letter.

By default, the ORDER BY sorts ascendingly, and it can grab the data type in the column automatically. Below is an example where I sorted the data on the left by column B, ascendingly, by alphabetical order.

And here’s how it goes when I change the sorting column to C.

So how do you sort descendingly? The answer is you add a DESC at the end of the ORDER BY (and for ascending it’s ASC).

7/Top 5, Top 10, Bottom 5, Bottom 10? We can do it all with LIMIT

This would be useful in many cases where you want to highlight the top performing employee in your company, the ones who earn most, the worst performing SKUs in your marketplace, etc.

We still using ORDER BY here, just add a LIMIT clause at the end to make the “Top”/“Bottom” thing work.

For example, here are the people in the top 5 for highest salary in my example.

And here are the top 2 only for Amazon.

And the bottom 7 overally.

8/GROUP BY - side by side with aggregated functions

In the 1st part, I have written about the aggregated function, and to make it work in a pivot style, we need the GROUP BY clause.

The rule of thumb here is: “every column in the SELECT clause (i.e. before the GROUP BY) must either be aggregated (e.g. counted, min, max) or appear after the GROUP BY clause” - as Ben Collins wrote.

I have extended my example to include the year of birth and month of birth for examples of this part.

Let’s say I want to know the average salary by year of birth, sorted ascendingly, the formula would be.

Or if we want to see the max salary by company, sorted descendingly, here’s how it goes.

To wrap this up, I show a table counting the number of people that was born in each month, by company.

9/Resources to learn further

There are a lot more of things to learn, that you can read via this article - about data manipulation inside QUERY.

https://developers.google.com/chart/interactive/docs/querylanguage#data-manipulation-functions

Also, these videos from Ben Collins might help.

Hope the 2 tutorials about QUERY would help you a lot in your specific use cases!

9 Likes

@Krivo, @Erni_D, @osxzxso here’s the 2nd part, hope it helps.

1 Like

@ThinhDinh thanks a lot for sharing. :+1::+1:

1 Like

This will help me a lot, I started studying this function yesterday.

thank you :+1:

1 Like

Can’t thank you enough for your contribution to these topics.

1 Like

You and Jeff are just simply amazing ppl when it comes to going out of your way to help others, and sharing your knowledge. If i can be of any help, do let me know. :blush:

4 Likes

@ThinhDinh Sorry for the late reply. My summer term recently started so my schedule has been very busy lately. Just looked over part 2 and, wow, perfect breakdown. There’s so much helpful information in there. :pray:

2 Likes

Have fun with the study and stay safe bro :smile:

2 Likes

@ThinhDinh saving the day again :slight_smile:
Thanks for this, I was overcomplicating my query with pivot and was stuck trying to filter the column name when I can simply use multiple group bys

your last example showed me the way

Thanks for the amazing sharing

Cheers

1 Like

My pleasure to help. Let me know if I can help you in the future.

1 Like