 What's going on everybody? Welcome back to another intermediate SQL tutorial. Today we're going to be covering partition by. Now partition by is often compared to the group by statement. The group by statement is a little bit different. The group by statement is going to reduce the number of rows in our output by actually rolling them up and then calculating the sums or averages for each group. Whereas partition by actually divides the results set into partitions and changes how the window function is calculated. And so the partition by doesn't actually reduce the number of rows returned in our output. Let's get started to look at the actual syntax of how to use partition by and then we'll compare it to the group by statement later just to see the differences between the two. We're going to be using these two tables on our left over here. So I'm going to pull those up really quick. So let's run this and let's look at the two these two tables side by well one underneath the other really quick. So what we're going to be using to demonstrate the partition by is this gender column as well as this salary column. And so we just need to join these two tables together on the employee ID and then we'll go from there. Now I'm not going to bore you with that. I'm going to skip ahead and we'll actually look at how to use this partition by. So I've joined these two tables together and this is our output but we don't want every single column. I'm going to start selecting some of these columns and then we'll start using this partition by and see what the output looks like after that. All right. So let's go right up here. Let's choose the first name. Let's do the last name. We'll do gender and let's do salary and now we want to identify how many male and female employees we actually have. And so we're going to say count of gender and this is going to be over and now we're going to do our partition by and we're also going to partition that by the gender as total gender. Now I'm going to come back to why we did each part but I want to see the output first and then I'm going to come back to why we wrote it this way. So let's just do this really quick. So it's going to be a little bit different than what you typically would expect in a group by statement. The group by is going to roll everything up and you typically wouldn't have like a first name last name in a group by statement because it would be very hard to roll all those things up into those individual columns and to reduce the number of columns that are in your output. And so in our output we can see Pam Beasley she's a female she makes $36,000 as a salary and there are three total women that work alongside her in this employee demographics table. And so in our total gender column over here this is where we use the partition by and if we used a group by statement to get this kind of information all we would be able to do to get this information in a group by statement is say select gender count of gender and then group by the gender down below underneath the join. So because we're using the partition by we're able to isolate just one column that we want to perform our aggregate function on and so we're able to add things like the first name and last name columns even though we aren't trying to include that in any partition or group by statement yet we're still able to add the aggregate function to each individual row while still maintaining those other columns. Let's take this entire query and let's basically just transform it into a group by statement and we'll see kind of what that looks like and what the difference is. So all I'm going to do is get rid of all this. I'm going to copy all of this and I'm going to say group by and I'm going to do that because we have to use all these columns in our group by statement. So let's execute this and as you can tell we are not able to see the output for the aggregate function that we were hoping for if we wanted to get the same output that we had before where we're showing three for females and six for males what we'd have to do is get rid of this first and last name and the salary and do the same thing in the group by statement and so let me get rid of these really quick and run this and so what the partition by is doing is basically taking this query right here and sticking it on one line in the select statement and so I hope now you can see how valuable the partition by can be if used correctly. Thank you guys so much for watching I really appreciate it. If you like this video be sure to like and subscribe below and I'll see you in the next video.