Over clause in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part one zero eight of sequel
civil tutorial in this video we'll
discuss the power and use of over clause
in sequel server the old laws combined
with partition by is used to break up
table data into partitions we have this
index here firstly specify a sequence of
a function we can use any of these
functions some min max etc and then we
use the over keyword followed by that
partition by and then by whatever
columns we want to partition that table
data we have an example here let's say
we have employees table and within that
table we have gender column so here
we're using the count aggregate function
over partition by gender so this is
basically going to tell partition the
employees table data by gender column so
it's going to divide that employees data
into two partitions male and female and
then this count aggregate function is
applied over each of those partitions
meaning we will get count of female and
male employees let's look at this in
action so here we have our employees
table here is the gender column what we
want to do is based on this table we
want to write a query which is going to
give us aggregated data by gender column
so we want the count of female and male
employees similarly we want to find out
what is the average salary for female
employees and male employees and
similarly their min and Max salaries so
at the moment notice we have got four
female and six male employees now we can
very easily achieve this using a simple
group by query we want the data to be
aggregated by gender column so we are
using group I on the gender column and
to get the totals for genders we are
using the count function to get the
average we are using average to get
minimum of salary we are using men and
to get the max we are using max okay a
simple group by query I have this exact
same query already typed here so when we
execute this we get the result that we
expect so straightforward so the data
that we see in the result set is
aggregated data now what if we want
along with that aggregated data we also
want non aggregate values like name and
salary
so if you look at this aggregated data
notice that we have four female
employees and six male employees now in
the results set right here we have the
names and salaries of individual
employees that's you know name and
salary is not aggregated okay so what we
want is this non aggregated values along
with these aggregated data for all
female employees look at that the grand
total I mean the gender total is four
for all male employees it is sex an
average salary for female employees is
five three seventy five so it's the same
for all female employees
similarly five thousand is the average
salary for male employees so the
aggregated data is repeated for each
employee so we want aggregated and non
aggregate data now the first thing that
comes to our mind is well why don't we
use name and salary column in the Select
list if we do that since this is a group
by query and name and salary are not in
group by clause and we are not using any
of the aggregate functions on those two
columns so we will get this error so
it's saying employees name so name
column is coming from employees table so
column employees dot name is invalid in
the Select list because it's not
contained in either an aggregate
function or in the group by clause okay
let's actually look at that in action so
if I try and include name comma salary
columns and then if we try to execute
this query we are going to get exactly
the same error that we have seen on the
slide so one way to achieve this result
is by including all these aggregations
in a sub query and then combining that
sub query with the outer query let's
actually look at that in action so what
I'm going to do is take this sub query
so when we execute this we get you know
average min max salaries and the count
aggregated by gender so I'm going to put
this in a sub query so let's turn this
into a sub query and let's call this
genders okay and then I'm going to use
an outer query so from the outer query
we
want the name of the employee we want
their salary and we also want their
gender and we want that from employees
table so I'm going to join this outer
query with the sub query so let's use in
a joint so we're joining employees with
gender so we need to specify the join
condition so on genders dot gender so
the common column between employees
table and this genders result set is
gender so general store gender equals
employees dot gender all right so what
do we want in the result we want named
salary gender we want gender totals
average salary means celery and maximum
salary these will come from our sub
query because that's what contains
allocation so when we execute this we
should get this result so what I'm going
to do here is use this genders result
set and it has got gender total
similarly generous dot average salary
and genders dot minimum salary genders
dot maximum salary all right let's go
ahead and execute our query and we get
an error and says ambiguous column name
gender so if you look at this gender
column it's present both in the
employees table and this genders result
set so we'll have to tell from where we
want that column we could either use
employee's table at this genders results
set it doesn't really matter for the
purpose of this query I'm going to use
employees table all right so let's
execute this query now and look at the
result this is exactly what we see on
the slide all right but look at the
amount of code we have to write to
produce this simple result set now it
will be much easier if we use over
Clause with partition by let's look at
that query now
so what do we want with the results that
we want actually I'm going to make a
copy of this so we want name salary
gender from employees table now along
with the gender we want gender total
average salary min and Max salaries so
to get gender totals I'm going to use
the count function and I'm going to
specify a column name here count of
gender we will use the over keyword over
and then followed by open parentheses
close parentheses partition by gender
column so what is this going to do this
is going to partition this employees
table data by gender so we will have two
partitions male and female and then this
count function will be applied for each
of those partitions so we are going to
get count of male and female employees
and let's give this column an aliyah's
let's call it genders todo so when we
execute this look at what we get we get
name of the employee salary gender
column and gender totals for female
employees and six male employees now
getting average salary and salary and
maximum salary it's pretty much
identical so I'm actually going to make
a copy of this B I'm going to now use
the average function but we want the
average of salary column so average of
salary over partition by gender and I'm
going to call this AVG salary okay so
similarly let's go ahead and do the same
thing for min and Max so minimum of
salary and let's call the column min
salary and here it's going to be max
celery and the function that we are
going to use is max all right let's get
rid of this last comma now when we
execute this query look at that we get
exactly the same result but look at the
amount of code that we have to write
this query is also easier on the eyes
it's very easy to understand
so partition the table data by gender
column and apply these aggregate
functions which you know will produce
the result that we want so here is our
over let partition by query thank you
for listening and have a great day