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

this is part 101 of sequences with
tutorial in this video well disk is
grouping sets in sequel server grouping
sets is a new feature introduced in
sequence over 2008 let's understand
grouping sets with an example we'll be
using this employees table in this demo
I've already created this table here is
the sequel script to create and populate
it with test data
I'll have the script available on my
blog in case you need it now based on
this employees table we want to write a
query that is going to calculate sum of
salary grouped by country column first
and then by gender column so the result
of a query should be like this so if you
look at the data here notice for female
employees in India we are paying 4000 as
the total salary for UK female employees
we are paying 5000 for us female
employees 12,500 so here
the salaries are grouped by country and
then by gender to achieve this we can
write a simple group by query as you can
see here within the result set we want
these three columns country gender and
sum of salary so within our select list
here we have country gender we are
applying the some aggregate function on
the salary column and we are giving that
column and a Lea's total salary from
employees table their first grouping by
country and then by gender I have this
group by query already typed so if we
execute this query we should get the
salaries grouped by country and then by
gender so along with this we also want
the sum of salaries grouped just by
country so if you look at the three rows
that are the green background color
notice that for Indian employees we are
paying 12,000 if you look at India
female employees we are paying 4000 for
India male employees 8,000 so if you add
those two rows together 4 plus 8 is
12,000 so for all Indian employees its
12,000 similarly if you look at UK for
UK female employees it is 5,000 for UK
male employees its 12,000 12 less five
is 17 so UK total salary by country
17,000 now to produce these three rows
that are with green background color
you know we can use another group by
query as you can see here so we want the
country so within the Select list we
want country now since we are grouping
here just by country gender is going to
be now so we are selecting null for
gender column and then the sum of salary
from employees and we want to group just
by country and between these two grew by
queries we are applying Union all
operator so this query is going to
produce this result set let's quickly
look at that in action
so I'm going to make a copy of this
query and then I'm going to make the
modifications so we want country gender
is going to be now because we are going
to group just by country so from our
group by Clause I'm going to remove
gender and between these two queries we
will apply the Union all operator but
before that if we execute just this
query notice that we get the total
salaries grouped only by country whereas
when we execute this query we get the
first six rows that is salaries grouped
by country and then by gender now if we
apply Union all operator between these
two group by queries we get the result
set that we see on the slide here now in
addition to these two result sets we
also want to compute total salaries
grouped just by gender column since here
we are grouping just by gender country
column is going to be now and to achieve
this as you might have already guessed
we are going to use another group by
query and that group by query is going
to be very similar to the group by query
that we have used to produce the country
groupings so I'm going to make a copy of
this second group by query so now
country is going to be now because we
are going to group just by gender and we
want the gender column and within our
group by we are going to use just gender
column so when we execute this we should
get the total salaries grouped by gender
and now between all these three grew by
queries I'm going to apply Union all
operator so when we execute this we
should get the result set that is he on
the sly
now in addition to these three results
ads we also want the grand total so here
we are not grouping by any specific
column we are not grouping by gender or
country we want the grand total of
salaries you know basically the total
amount of salary that we are paying for
all employees across the globe to
achieve that I'm going to use another
select query now we don't require any
group by clause at all you know country
is going to be now gender is going to be
now and we just want some off salary
column across the entire table so when
we execute this we should get the total
salary that you are paying for all our
employees so now we are going to apply
another Union all operator here so when
we execute this we should get the result
that we see on the slide now if you look
at this query we definitely have two
problems here first the amount of
transactional code that we have to write
is huge second to produce this result
this transaxial code is going to hit
that employees table four times so it's
not very good from a performance
standpoint as well now if we use the
grouping sets feature introduced in
sequel Server 2008 then the amount of
transaxial code that we have to write
will be greatly reduced this is just the
amount of code that we have to write to
produce this same results hat ok so
let's look at that grouping sets query
so within our result set we want those
three columns country gender and total
salary so the Select Clause is not going
to change country gender sum of salary
we are giving it an ally as total salary
from employee's table we are still going
to use group by because we use grouping
sets with group by so group by and
grouping sets so we specify our grouping
sets here so we have an open and a
closed parenthesis and then we specify
our groups so we want to first group by
country and gender so the first six rows
here will be produced by this first
grouping here okay
this six rows you know have the salary
grouped by country and gender and this
is the group that will produce that and
then we are grouping by country so the
next three rows that we have here so we
have got three countries India UK USA so
salaries grouped by country and this is
the group within our grouping sets
that's going to produce or three rows
and then the rows 10 and 11 are produced
by the third group that we have here
gender and then to produce this grand
total right here we're using this empty
parentheses so this is basically telling
to sequel server we want the grand total
of some of you know salary column all
right so let's look at this in action
let's fire up another query editor
window and I'm going to make a copy of
this select query so we want to use
grouping sets open parentheses close
parentheses and then we specify other
groups first we want to group by country
and gender so this is going to kill the
first six rows and then we want to group
just by country and then just by gender
and then finally grand total so when we
execute this query we should get the
result set that this Union all operator
has produced now if you look at the data
first of all look at the number of rows
the Union all query has produced twelve
rows they grouping sets query also
produce twelve rows but if you look at
it's the same data but if you look at
the order of the rules here between the
grouping sets query and the Union all
creates not the same now if you want to
control the ordering of rows you can use
order by Clause with this grouping sets
query so for this query you know
basically to produce the data in the
same order as this Union all operator
query I'm going to use order by
and we are first going to order by
country grouping and then by gender
grouping and with any gender grouping we
want to order the genders okay
so order by I'm going to use the
grouping function and we want to group
first by country grouping and then by
gender grouping and then finally by
gender so when we execute this now the
ordering of the data should be same as
well between these two queries thank you
for listening and have a great day