All right.
So in this video, we're going to learn about the over clause, which is critical to working with window
functions.
But before we do that, I want us to get on the same page in terms of data.
So you can follow the videos and type this out yourself.
But I would just look at the code that I've included along with this video and run this.
So we make a new table employees.
It's simple.
Employee number department is var char salary is an integer and then we insert just a handful.
Right.
So some engineering salaries, some sales salaries and some customer service salaries.
So I'll go ahead and do this.
Just I'll make a new database, create data base.
What should we call this?
Just I don't know.
We'll call it salaries.
Or no, we'll call it Window Funk's, whatever you want to call it.
You don't even have to make a new database.
I just already had an employees table in my previous database, so I'm going to use this new one window
funks.
All right?
And then I'm going to paste that code to create the table and insert into employees.
I'll do a select star from employees.
And there we are.
We see employee number, department number or department name and salary.
OC So as we saw many times in this course, we could do something like select department comma average
salary from and then employees group by department.
And we discussed this in the previous video.
This works.
This is great.
This is the easiest way to see a single boiled down number for each department's average salary.
So we get three rows, one row that was collapsed down from each of three groups because we only have
three different departments.
So we had different three different groups.
And those groups had know, I don't know, five, six, seven different entries in each group, different
rows all collapse down to a single number for each group.
Now we could even boil it down further and do this select and then just average salary from employees.
And that boils down all of the rows into a single average $68,428, five point or 0.5 $0.07.
I guess we could round it, but we didn't.
That is the average of all of the salaries.
So nothing to do with window functions, just an aggregate function average.
But if I alter my syntax a tiny bit and I include the over clause with parentheses, after an aggregate
function like average, we will get a different result.
So if I try that select average and then over from employees, huh.
Now what we get is the same number 68,428 six 8428.
But we get one value for every single row in our data set or in our selection, which in our case we
selected everything from employees.
So remember how these window functions work.
They take our data set, form it into one or more windows, which we'll talk about.
Think of those as groups.
Run some code with those windows, get a value back, gives us that value with each row.
It doesn't collapse those groups or those windows.
So what we did here when we said select average salary over when you do over with empty parentheses,
that sets up or constructs a window.
But we left it empty, which means a window that includes all the rows, all the records.
Shortly we'll learn how to partition, how to create different sub or smaller windows.
But for now we just said find the average salary for all of our rows together and give us the value
alongside each row.
Now, we didn't select anything else.
We only got the average salary.
But what I could do is this select employee number, salary, department, even.
Let's do department first.
And then average salary over empty parentheses from employees and employees.
And what do we get every row that we selected, which is all rows?
We don't have a wear in there.
We didn't narrow it down in any way.
We have the employee number, the department, the salary, and then also the average for all of our
salaries, which maybe it's kind of weird to do that.
Maybe not.
It could be useful to be able to see individual salary alongside average salary.
But I'm going to show you how we can get more detailed statistics, how we can break this down by department,
for example, and see each department salary alongside each individual salary.
But this is our first step.
By using over, we created a window.
Think of that as a group, a big group, and we didn't specify anything inside the parentheses.
We don't know anything yet.
And so that tells my SQL.
This window includes all the rows, find the average of salary across all the rows, and because we
used over, it doesn't reduce us down.
It doesn't take that window and turn it into a single row.
It gives us that value for every single row that was in the window.
So we also can do this with all the other aggregate functions we've seen so far.
Let me just put this in a file so we have a record of it.
So something like we've seen some, right?
We've seen Men and Macs.
Just show that real quick.
If I want to see the minimum salary from the entire table, this is what I would do.
And then I'll also do the max.
Max salary over.
And to that over makes a big difference because now we see men and Macs alongside each row.
So I can look at, all right, this person made $80,000 as a salary.
The minimum salary in the database is 30.
In the table, 31,000.
Maximum is 159,000.
And we see that for every single row.
And I understand it doesn't seem that useful to have the Min and Max column for every single row just
always be the same.
But it is still different.
And it is it is somewhat useful.
If we wanted to be able to compare somebody's salary to the average, the min and the max, we don't
have to do two queries.
We can look at every salary and see aggregate statistics alongside, but we'll have more control in
the coming videos.
So to sum this up, what we saw, if I get rid of over, let me just show what happens then I'll keep
this version.
And I'll get rid of over there.
And just do min salary and max salary.
We know how to do that, Min and Max, but we're going to have a bad time.
Because we're trying to do a group by expression, but we're also referencing columns that are not part
of the group by they're not grouped together values.
So we've learned about this earlier, right?
If I do a group by even though I didn't explicitly write the clause group by it still is grouping behind
the scenes.
When we do Min and Max, it's trying to do some aggregate stuff and boil it down to a single value like
if we did this.
31,150 9000.
That is how men and Macs work.
When we don't call them as window functions, we get a single value.
So we were trying to get a single value min and max alongside all the rows of salary department, all
those values.
And the way to do that is by using over.
So this is going to give us the same number.
At the end of the day, 31,150 9000.
But the output is very different and soon we'll see that the way that it's actually performed is very
different as well.
So Min and Max Sum, all the different aggregate functions work.
I don't need to I don't think I need to show them all to you.
And there's also a handful of window only functions that we're going to learn about in this section
as well.
But so far we've seen things like average men and Macs.
Those are regular aggregate functions.
You don't have to use over with them.
But if you do, we get this very different result.
So next up, we're going to learn about what we can put inside of over.