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

this is part 1 0 2 of sequel server
tutorial in this video we'll discuss
roll up in sequel server role app is
used to do aggregate operation on
multiple levels in a hierarchy let us
understand this with an example we have
this employee's table here notice we
have got employees in three different
countries US UK and India now based on
these employees table we want to write a
query that's going to retrieve salaries
grouped by country along with grand
total so the result of our query should
be like this notice for Indian employees
we are paying twelve thousand for UK
employees seventeen thousand u.s.
employees twenty-two thousand five
hundred and then we have the grand total
and if you look at the grand total row
the country column is now that's because
for grand totals we are not grouping by
any specific column and then we have the
grand total there are several ways to
produce this result but the easiest and
best way is by using roll up with group
I so within our result set we want two
columns the country and total salary so
we are selecting country column from
employees table and then we are applying
this sum aggregate function on the
salary column and we are given that an
aleeah's total salary from employees
grew by and we are using roll-up so we
want to roll up on the country column so
that's going to give us subtotal as well
I have this exact same query already
type so when we execute this we get the
salaries grouped by country as well as
the grand total now we discuss that
there are other ways of doing this as
well now we can rewrite this group by
query in a slightly different fashion so
I can also write this query like this
group by country column and I can use
width roll-up so now when we execute
these two queries we should get exactly
the same result now we can also achieve
this by using Union all with group I so
let's make a copy of this so now when I
remove this with roll-up from this query
and when we execute
we are going to get the salaries grouped
only by country now if we want the grand
total I can write another query so let's
make a copy of this one
now within grand total the country
column is going to be null so let's
select null sum of salary as total
salary from employees and for grand
total we are not going to group on any
specific column so I'm going to remove
group I so when we execute this query we
are going to get the grand total now we
will apply the Union all operator
between the group I and our grand total
query so when we execute this we get
exactly the same result now we can also
use grouping sets to achieve exactly the
same thing so let's again make a copy of
this query so we want the same two
columns from employee table grew by now
we want to use grouping sets and then we
define other groups so we want to group
by country so when we execute just this
grouping sets query we are going to get
salaries grouped only by country notice
we don't have grand total
now to get grand total we are going to
use empty parentheses so this is going
to specify that we want the grand total
so now when we execute this we should
get salaries grouped by country plus the
grand total so there is several ways of
achieving the same result but the
easiest and best way is by using roll-up
with group I now let's look at another
example so now we want to group salaries
by country and then by gender and we
also want subtotals at country level as
well as the grand total so if you look
at the result set right here notice for
Indian female employees we are paying
four thousand for Indian male employees
eight thousand and for all Indian
employees so that's the subtotal for
India Country
so we are paying 12,000 similarly for UK
female employees 5,000 UK male employees
12,000 when we add them together 17,000
so for all UK employees it's 17,000
so we have the subtotals at country
level same is the case for USA as well
and then in the end we also have the
grand total if you look at the subtotal
rows notice gender column is now whereas
if you look at the grand total row both
country and gender column is not because
the grand total we are not grouping on
any specific column so again to achieve
this result there are several ways of
doing it but the best and easiest way is
by using roll-up with group I so this
query is very identical to the previous
query the only difference is in the
Select list so within our result set we
want the gender column as well so along
with the country column we included the
gender column and then in our to a
rollup function along with country we
are also passing the gender column those
are the only two differences okay
so let's quickly look at that in action
so within our select list we want the
gender column and we also want to roll
up on gender column so when we execute
this we should get this salaries grouped
by country and the subtotal for country
and then basically the grand total as
well very easy now we can also rewrite
this group by query like this so with a
no result set we want gender and here we
will say group by country gender with
roll-up again you know when we execute
these two queries the same result so if
you look at the messages look at that
tendrils chain rules and the output is
exactly the same thing all right now
let's see how to do the same thing using
Union all so the first query here you
know this is just going to produce total
salaries grouped by country but within
our result set we also want the gender
column so let's specify the gender
column right here
and then we want to group by gender as
well so when we execute this we should
get the salaries grouped by country and
by gender now we want the salaries
grouped by country that is the country
subtotals so in this case we are going
to select country gender is going to be
now and then sum of salary from
employees and then we are going to use
group by just country so this one is
going to give us salaries grouped just
by country so these are like country
subtotals and then finally we want the
grand total and getting grand total is
very easy let's use another Union all
operator and let's make a copy of this
now when we want the grand total we are
not going to group by any specific
column so we will not have the group by
country is going to be null gender is
going to be now and we get the sum of
salary so now when we execute these
three queries we should get the same
result but one thing to keep in mind
here is the order of the rows in the
result set is not the same to control
the order you can use order by Clause we
can also use the grouping sets to do the
same thing so at the moment when we
execute this we are getting only the
salaries grouped by country and then the
grand total now first of all with no
results that we want gender as well so
within our select clause I'm going to
specify gender column and then we want
to first group by country and gender
okay and then we want to group just by
country so that's going to give us the
subtotals for country and then we want
the grand total so when we execute this
again the result should be identical
look at that we have the salaries
grouped by country and gender then the
country subtotal similarly for UK and
USA and finally the grand total
so we have those queries here using
roll-up claws with glue by using Union
all that grew by and finally using
grouping sets thank you for listening
and have a great day