SQL Essentials: GROUP BY vs. PARTITION BY explained

Source: google.com & edited with 💓 by author

If you’re not a Medium member, you can still enjoy the full article for free. Click here to read it all 😊

When I first stepped into the world of data, I was a college student captivated by the simplicity of SQL. The language seemed straightforward, almost like plain English. With commands like SELECT, what you see is what you get. There are no hidden meanings, no complicated syntax.

This initial attraction started to waver when I encountered my first challenge: WINDOW FUNCTIONSa (I have a good article about these right here 😄) and their PARTITION BY clause. Suddenly, the clear skies of my SQL journey were clouded with confusion, and stormy thoughts of complex queries loomed ahead.

But don’t worry! It’s not as daunting as it seems. If you’re new to the data realm, there’s nothing to fear; with a bit of patience and practice, you’ll master these concepts too. In this article, I will guide you through the differences between PARTITION BY and GROUP BY, helping you understand their unique roles and use cases.

— — Ready? Open your IDE and get started — —

BY…Not the Same Thing?

Just because both clauses contain ‘BY’ doesn’t mean they do the same thing. It’s a good thing they don’t, as having dedicated functions for specific tasks makes SQL more powerful and versatile.

At first glance, the results of using these clauses might seem similar, but there are key differences; it’s quite important to know the difference between these 2 because you’ll know when to use one of them.

How about having a 1-to-1 meeting with each?

Let’s do a GROUP

The most important and awesome thing about a GROUP BY clause is that it’s pretty rebellious and wants to be used separately (this guy seems to be a bit of an introvert, right? Me too 😄)

Here is an example:

Source: Made with 💓 by author

As you see, the GROUP BYclause is used independently and required by SQL when…