Group By and Aggregations in PostgreSQL

Alex The Analyst

Create the Database with this script: https://github.com/AlexTheAnalyst/PostgresqlYouTubeSeries  Practice Postgresql here: https://www.analystbuilder.com/questions  In this series I want to teach you Postgresql! It&#039;s one of the most popular types of SQL right now and is becoming even more popular with the rise of AI. I&#039;ll teach you all the syntax and how to use it as well as go beyond just querying! ____________________________________________   RESOURCES:  💻Analyst Builder - https://www.analystbuilder.com/  📖Take my Full MySQL Course Here: https://bit.ly/3tqOipr 📖Take my Full Python Course Here: https://bit.ly/48O581R 📖Practice Technical Interview Questions: https://bit.ly/46pDqqL  Coursera Courses: Google Data Analyst Certification: https://coursera.pxf.io/5bBd62 Data Analysis with Python - https://coursera.pxf.io/BXY3Wy IBM Data Analysis Specialization - https://coursera.pxf.io/AoYOdR Tableau Data Visualization - https://coursera.pxf.io/MXYqaN  *Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!* ____________________________________________   BECOME A MEMBER -   Want to support the channel? Consider becoming a

Transcript

00:00What's going on, everybody? Welcome back to another video. Today, we are continuing our

00:03PostgreSQL series, and in this lesson, we're going to be learning about aggregations and

00:08group by. Now, some common aggregations in SQL are going to be things like min, max, the average,

00:14the count, and the sum. If you've used Excel, you've probably seen these. You've probably heard

00:19of them before. They're very popular, and I think they're pretty straightforward. But once we get

00:22to the group by, it gets a little bit more difficult, but I'll walk you through it. We'll

00:27understand it really well by the end of this lesson. Now, min, max, average, count, and

00:32sum. These are the ones that you're going to use 99% of the time. Let's come right down

00:37here, and let's take a look at this estimated net worth column. In fact, let's bring this

00:42up just a little bit. Let's say we want to take a look at the minimum value that is within

00:48this estimated net worth. So what we're going to do is we're going to say minimum, so that's

00:52min, and then we're going to open up these parentheses, and this is where we select what

00:57column we actually want to look at the minimum value. So let's go and write estimated net worth.

01:05Let's go ahead and run this. As you can see, the value is right here. It's going to be 500.

01:10Now, we can alias this. If we want, we can say minimum net worth if we want to write a

01:18column.

01:18I'm not going to do that for all of them. I'm just showing it that you can do this.

01:23Now, notice we're not selecting everything and then putting the minimum estimated net worth at

01:28the end or something like that. We are simply returning this value. One thing to note is we

01:34can't add other columns like this. The reason for that is because that has to be in a group by.

01:40It

01:40even says it right here in the error. It says this has to appear in a group by clause or

01:45be used in

01:45aggregate function. But since we're not doing that, we cannot see that. Let's go ahead and look

01:51at the maximum value. So we're going to say max. And I'll get rid of this as we won't use

01:56that

01:56anymore. But now we're looking at the maximum value for estimated net worth. I think that's 10

02:02million or something like that. It's quite high. Now, all the min and max are doing these are by far

02:08the simplest. All they are doing is looking at all of them and taking the highest or the lowest.

02:12That's all we're doing. Now, I'm going to copy this real quick just so I can look at everything

02:20and pull this back up. And we'll pull this down just a smidge. Let's pull everything back up because

02:28the next one is average. It's going to add up all of the values in this column. And then it's

02:34going

02:34to divide it by how many rows we actually are adding. That's how we get our average. So let's

02:42come right here. And let's look at the average net worth. So this takes into account all of our

02:46rows of data. Then we see what the average net worth of all of those people are. Let's go ahead

02:51and run this. Now, it looks like it's around 2,008,666. So it's around 2 million. But if you

02:58do

02:58look at this, you'll notice that most people have far less than 2 million. Darth Vader is really

03:05pulling it up. Leia Organa is really pulling up that average. So is Padme Amidala. Everyone else is

03:11kind of pulling it down quite a bit. But they're so high that they're bringing that average up

03:14quite a bit. Now, count is a really good one. I use count for a lot of different things,

03:20especially when we start using group by. Count is super useful. And we'll take a look at that

03:24in a little bit. But count is just going to count the rows within that column. If we do it

03:30like this,

03:31let's go ahead and run this. We have 12 rows or 12 values within that column. And that looks great.

03:37But what happens if we have some null values in here? Because we have null values right here

03:43in our ship ID. Let's try it out. So let's run this. Now we only have nine. So it did

03:52not count

03:53the values where we didn't have any data in it. Now, if it had data like a zero, or if

03:59it had any

03:59other data in it, it wasn't null, it would count it. But we have one, two, three nulls, and we

04:04have 12

04:05rows of data. So we only had a count of nine for that column. That is a really important thing

04:10to

04:10note. The last one is sum. And this is probably the easiest one. It just is going to add up

04:16all

04:16of the values. That's it. Let's go ahead and run this. And we're doing a sum on the ship ID.

04:21That's the wrong one. Let's do estimated net worth. It wasn't the wrong one. We just, I wanted to look

04:28at the other one. Looks like it's around 24,104,000. That's what the sum of all of their estimated

04:36net

04:36worths were. Now, that is kind of a really quick baseline of our aggregations. But GroupBuy really

04:44allows us to dig into our data a lot more. Now let me pull this up. So this is an

04:50example of how

04:51GroupBuy works. So over here, we have title, genre, and quantity. And we're going to be grouping on

04:56this genre right here. You can see our genre over here. Now all it's doing is a summation.

05:02So we have adventure, and we have adventure. And we're grouping, you can see these red arrows,

05:07it goes down to one row of data. And then it adds up, it performs our aggregation. So we're summing

05:14the four and the three, and that's going to equal seven. So it doesn't matter how many rows we have

05:19for adventure. It could be five, 10, 10,000, 10 million. When you group by adventure, it's all going to

05:26go

05:26into one row. And that's the powerful thing about using GroupBuy. So let's get rid of this.

05:32Let's come in here and start using GroupBuy. Now, what we have to do is we have to group by

05:37a column,

05:38and then we have to have our aggregation. So let's say we want to look at species. Within species,

05:44we have several different. We have human, we have Wookiee, we have droids. Let's group by our species.

05:51Now, let's just start with the simple one. Let's just look at the minimum value for estimated net

05:57worth. So we're going to say minimum value for, and I should have just kept that from earlier,

06:04but we're going to look, because I'm bad at spelling, we're going to keep the estimated net

06:08worth. So now it's going to look at human, and we have many of them, and it's going to see

06:14what is

06:15the minimum value for all the humans, but only the humans. And then it's going to look at Wookiee.

06:20It's going to say, what's the minimum value? Then what's the droid? So for droid, you can see it's

06:25the 3PO poor guy. He's only worth about $1,500. And then human, it looks like Luke Skywalker at $150

06:32,000.

06:32Wow, all of our humans are very wealthy. So now we've selected our species, and we're looking at the

06:37minimum estimated net worth. Let's just try running this. Let's see what happens.

06:41It's not going to work, because we have to use that group by. I told you that earlier. You should

06:47have caught that. I blame you for that one. I'm just messing. All right, now we're going to actually

06:52use group by. So now that we have our species, and we have our minimum estimated net worth,

06:57we have to group on the species. That's all we have to do. But we have to have it in

07:04our select

07:04statement, because we're selecting the species, and we're grouping on the species. So before the

07:09aggregation right here, we have to have the same columns. All right, now let's try running this.

07:16So now we have unknown, droid, zabrak, wookie, gungan, and human. Human with $150,000,

07:24then droid with $1,500, and then unknown, zabrak, and wookie, and gungan. They only have one row for

07:30each. So those aren't the ones that we're going to be focusing on right now. Now I'm going to order

07:34this. I'm just going to say order by, and we're going to do species, and we can do it just

07:40ascending.

07:41I think that'll be fine, just so that we have some consistency in our output. So now we have droid,

07:47gungan, human. So there's human, there's our droid. Those are the two that we'll be focusing on. I'm not

07:51going to filter on them, just so you can see all of it. But just so you know, we don't

07:56just have to do

07:57one aggregation. We can do multiple aggregations. So let's take the maximum net worth. And let's run

08:05this. And now we have our species, our minimum value, and there's our maximum value. So we can

08:12just keep going with this. I'm actually going to do a enter and then a comma. And now we're going

08:18to do

08:18the average net worth. I'm just going to paste this in there. So now what we're doing is we're

08:26taking all the humans, we're adding up those values and dividing it by how many humans we have.

08:31And this becomes our average. Now we're looking at the average for each of these species. This is the

08:37average for the droids. This is the average for gungan, which this one, the unknown, and the Wookiee is

08:42always going to be the same. Oh, and Zybrek. That's always going to be the same. But if you have

08:46multiple values, then we're going to get a true average. And so right here and right here,

08:51these are the averages for the human and the droid. Let's add in our next one. And we can add

08:58in count. This one should be really helpful to actually see, hey, how many values are in each of

09:04these? Here we have human, we have six humans, we have gungan one, one, one, one, and then of course,

09:10droid two. So this is really useful because we can see how many rows of data do we have where

09:15there

09:16are humans in the species? How many rows of data do we have where there are droids for the species?

09:20So these are extremely helpful. And then of course, we have the sum. And this is our last one.

09:26And we're going to put this right here. There we go. And let's run this. And so this is the

09:33sum.

09:33This is adding up all the values. So we can see that the human, they have a massive net worth,

09:37while almost everybody else is, I would say, you know, Wookiee and Zybrek. They're okay.

09:43But gungan and droid, they're extremely poor. So this is super powerful when you're working with

09:49large amounts of data. Now at the end of the series with beginner, intermediate, and advanced,

09:53we'll have a full project where we'll be using hundreds of thousands of rows of data in our

09:57PostgreSQL database. And knowing how to do this will be so useful. It's even useful here, but it's

10:03even more useful when you have large amounts of data. Now so far, we've only grouped by our species.

10:10Let's look really quick. Let's take a look at all of our data. We can group on multiple columns at

10:17the same time. And it's actually really useful. So what we're going to do is we're going to take

10:21a look at the species like we've been doing. But now we're going to break it up by the planet

10:25ID.

10:26Now, the only one that we're going to be able to really see this one, and this is planet underscore

10:31ID, and we have to add it up here. So we do planet underscore ID. Let's go ahead and run

10:37this and

10:37we'll be able to break it up. But the only one that has multiple, and I need to add a

10:40comma,

10:41whoops. Let me redo that. Hey, listen, it happens. The only one that's going to have multiple is

10:48right here. So this is the human they live on planet four. If we went and looked at our planets,

10:53you'd be able to see what value that is. We're going to look at that in our joins, which is

10:57the

10:57first lesson in our intermediate series. When we join together, we can look at data from multiple

11:01tables at one time. But within humans, we're breaking out by human and we're also breaking

11:07it out by the planet ID. So we can look at the species first, then we can order by the

11:13planet ID. So we'll order by planet underscore ID. And we do this both in ascending. And let's

11:21run this again. And so now within a human, we have one, two, three, and four. The only one

11:27that has multiple is this one right here. So we are grouping on species, and then we're

11:31also grouping on the planet. And it depends on your data. If there's a lot of columns with

11:36repeating values where you want to break it out by multiple things, this is how you do

11:40it. And so now we're saying, okay, for the humans on planet four, this is their minimum

11:45value. This is their max. This is their average. This is how many people there are. This is the

11:49sum of those values. Now we're just grouping in multiple columns. So it's a more specific

11:53subtype of data that you're actually aggregating on. So that is aggregations and group buy in

11:59PostgreSQL. I hope that you learned something. If you did, be sure to like and subscribe and

12:03I'll see you in the next lesson.

Category

Transcript

Comments

Recommended