The next thing we're going to cover is the partition by clause, which we put inside of over.
If we want to have a window function, work with multiple windows.
We call them windows, not groups.
It's the same concept though, right now.
When we don't have anything in those prisons, we are finding the min salary and the max salary for
all of our salaries.
But if I instead want to find the minimum salary for each one of these departments in the maximum salary
for each department, I would then partition by department when I use partition by instead of having
one massive window with all the rows, we get smaller, presumably multiple windows.
If we have different values for department, one window might have 20 rows, another one might have
five rows.
It's just basically the same concept as grouping.
We call it Windows, and then each one of those windows will have whatever operation performed on its
values, like average across the sales department window, average salary across the customer service
department window.
These chunks of rows average for that, average for these salaries.
And then just like as we saw before, our output will include every row alongside whatever we calculated
using a window function.
So let's take a look at this.
Let's write a select employee number.
We'll look at every employees number and their department and their salary alongside the average salary.
But instead of saying over and having empty print, meaning the entire all the rows is one window,
let's partition by and this doesn't have to be capitalized, but I do it partition by department.
From employees.
All right, so if I run this one.
Take a look at what we get now.
Let's rename this.
That's a mess to look at.
We could put these on different lines, clean it up a tad.
And we'll call this as department average.
All right.
So now for every single row we see the employee number, the department and that individual's salary
and then the department average.
So all of the rows that have Department of Customer service, their average is 46,571.
All the engineering rose, all those employees.
The average salary is 81,285 sales.
Once again, 77,228.
We see individual rows with an individual salary alongside some aggregate information, in this case,
the average by department.
So we can't do this with group buy.
I could, of course calculate the average by department very easily.
We could do this select department and then average salary from employees group by department and we
get the same numbers.
But the output format is very different.
We're not seeing this alongside each of those individual rows.
Instead, all of our groups are collapsed to a single row.
So this lets me see this person here made 80,000.
As an engineer, how does that compare to the department average?
It's pretty close.
But then this person in sales.
Wow, $159,000.
How does that compare to the department average?
It's not close at all.
They're an outlier.
So we could also do things like include the average salary for all employees.
If once again, I don't put anything in over, we'll call this as company average.
And I can have multiple of these window functions in the same output.
So now we have the department average and the company average.
So let's try another example.
If I want to calculate the I don't know how about how many employees are in each department?
This is easy enough.
I can do a select employee number.
I don't know if we need all this department and salary, but then I can also do a count and we'll just
count the rows over and we'll partition by.
Department again.
And we'll call this as department count.
Sure.
From employees.
And if I run that, we see how many individuals we really have.
Seven in every single department, huh?
Well, that's anticlimactic, but you could see our count per department.
It's just hard to know that it's working.
But I guess it is.
We have seven in every single department.
Let's try another example.
Let's do some.
Let's sum up the payroll for each department.
And I'm using department because that's really all we have in this very simple data set.
So I'll keep this same select.
We'll take every employee number, department and salary, but then instead of average, we'll do some
let's do our total payroll.
So that would be some.
Salary over one massive window.
No partitions, just one big window with all the rows as total payroll.
And then let's do a similar sum salary.
But we'll partition it.
Partition by department.
As department payroll.
And here we are.
We see each employee, their number, their department, their salary, then the department payroll.
So customer services payroll is $326,000 a year.
And then the total company payroll is $1.4 million a year.
Same thing down here.
Engineering, we see 569,000 is the department payroll for each individual.
We see their salary and then we also see the total payroll.
And this is going to be the same for every single row.
So I hope I know you're able to see sort of how this works.
Maybe wrap your head around this concept of taking our data, forming it into these windows that we
partition by in some way.
We don't have to partition, but often we do.
And then some calculation is run on each of those windows average salary for each window.
And then instead of being collapsed down to a single row, all of our information is preserved as individual
rows and then we get aggregate information added on.
Okay, so that's how we can use partition by to form these smaller sub windows to group our rows together.