GROUPING ID function in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part one zero six of signal
server tutorial in this video we'll
discuss grouping ID function and sequel
server the difference between grouping
and grouping ID functions and the use of
grouping ID function this is
continuation to part 105 so please watch
part 105 before proceeding grouping ID
function computes the level of grouping
first let's look at the difference
between grouping and grouping ad
functions and then we'll discuss how to
use grouping ad function to compute the
level of grouping the first difference
is from the syntax point of view
grouping function is used on a single
column as you can see here whereas the
column list for grouping ad function
must match with the group by clause
column list whenever we use functions
like roll-up cube grouping sets we use
those functions in conjunction with
group by clause and with group by clause
we specify the list of columns by which
we want to aggregate the data so if we
are using grouping ID function in such a
query then the column list that we
specified for the grouping ID function
must match the column list that we
specify in the group by clause otherwise
we may get inconsistent result we'll
look at an example of that in just a bit
grouping function indicates whether the
column in a group by list is aggregated
or not
grouping returns one fer aggregated or
zero for not aggregate in the result set
we discussed grouping function in detail
in our previous video session grouping
ID function on the other hand
concatenates all the grouping functions
perform the binary to decimal conversion
and returns the equivalent integer in
short we can say grouping ID of a comma
B comma C equals grouping of a plus
grouping of P plus grouping of C let's
look at an example and that should make
all these points clear let's flip to
sequel server management studio this is
the same query that we wrote in the
previous video session so if you look at
what this query is doing it's giving us
total sales across these three
dimensions continent country and set a
and then we are using grouping function
on these three columns continent country
and city if you look at row number one
grouping of continent
Andrian city or all of those functions
return zero because in this case the
data is not aggregated by any of those
three dimensions whereas if you look at
row number three the data here is
aggregated by city column so grouping of
City returns one whereas grouping of
continent and grouping of country
returns zero but if you look at row
number seven here here the data is
aggregated by country and City columns
so grouping of country and grouping of
city returns one whereas grouping of
continent returns zero and if look at
the grand total row here the data is
aggregated by all the three dimensions
so grouping of continent country and
city all three of them returns one so
here by using grouping function on a
single column now what I want to do is
concatenate these you know bits you know
which are displaying 0 and 1 together to
form a single binary string so to do
that I'm going to say grouping of
continent apply a plus operator and
grouping of country another plus
operator and then let's give this column
and Aaliyah's let's call this maybe
groupings so when we execute this I
expect a binary string for example for
Row 1 it should display 0 0 0 and for
this row it should display 0 1 1 you
know the binary string basically well
let's execute this look at this we have
a problem here instead of displaying a
binary string it's actually adding those
bits together so for row number 7 it has
added you know country city and
continent 0 plus 1 plus 1 and it gave us
2 instead of concatenating them so to
force those bits to be concatenated I'm
actually going to convert you know the
result to end where tab and for that I'm
going to use the cast function so cast
grouping of continent as and we're care
of one ok and let's do the same thing
for the other grouping functions that we
have so let's call pay
this rest of the expression from there
alright so now let's go ahead and
execute this now notice that we cat they
concatenated bits okay so this is the
binary number that we are getting now we
discussed that grouping ID of a comma B
comma C equals grouping of a plus
grouping of B plus grouping of C now
here we have grouping of a that is
Continent grouping of P country grouping
of C city okay we have that binary
string now let's use grouping ID
function now one point to keep in mind
you know the column list that we specify
for grouping ID function must match with
the column list that we have in the
group by clause okay
so let's use grouping ID function here
grouping underscore ID and then we have
to specify the column list so I'm going
to use the same column list along with
our grouping ID function and let's give
it an alias let's call it grouping ID TP
ID so let's execute this now and notice
we get a grouping ID column value and if
you look at this column ID value this is
an integer now what did we discuss
grouping ID function concatenate all the
grouping functions perform the binary to
decimal conversion and returns the
equivalent integer so here we are
getting any an integer but this one you
know is a binary string so if you can
with this binary number to a decimal you
know we get a value of three now if
you're curious about how to do that
conversion it's straightforward so here
we have all zeros but if you look at
this row right here it's 0 1 1 so let's
do the mathematics for that to compute
that to a decimal number so we have 0 1
1 we start from right to left so to
power 0 into and we multiply that with
the bit value the bit value here is 1 so
to power 0 x 1 similarly here it will be
2 power 1 x 1 because the bit value is 1
and this will be
to power 2 x 0 and that's because here
bit value is 0 so it starts from 2 power
0 and it goes on to point 1 to point 2
to point 3 so from right to left and
then if you have a bit value of 1 you
multiply that to power whatever power
value have with that bit value here it
is 1 so we multiplied with 1 here it's 1
we multiply it with 1 here it's 0 so we
multiplied it lets 0 now power 0 is 1 1
multiplied by 1 is 1 similarly to power
1 is 2 multiplied y 1 is to 2 power 2 is
4 x 0 is 0 and when we add these values
together 0 + 2 + 1 . is 3 so if you look
at that true right here where we have 0
1 1 so that's what the grouping
functions have given us the binary
string and the equivalent until the
value returned by grouping ID function
is 3 and if look at this grand total row
look at the bit values that we have it's
1 1 1 so when we do the same mathematics
4 1 1 1 we should get a value of 7 so
these two are not going to change
because they're 1 1 and this will be now
1 so 2 power 2 multiplied by 1 because
the bit value here is 1 so 2 power 2 is
4 4 x 1 is 4 so 4 + 2 + 1 equals 7 and
that's what we see right here so that's
how the grouping ID function in our
retrieves the value now we also
discussed that if the column lists that
we used with grouping ID and if it's not
identical with the lists that we haven't
grew by we may get inconsistent results
and that's going to happen if we alter
this so if I remove country here I mean
continent from that grouping ID but our
group by column list is different so
when we execute this look at what we are
getting we are getting different integer
values okay
alright now we know how to compute you
know the grouping ID column how sequel
server computes that grouping ID column
value and if you look at let's put this
continent back so the beauty about using
this grouping ID function is that it
gives us the level of grouping so
whatever grouping ID column value you
have here that specifies the level of
grouping that means we can use this
column to sort or filter by level of
grouping for example so we have just
seen that how we compute the grouping ID
column value now if you look at the data
that we have here notice we have sorted
the data by grouping ID column let's
actually do that here in real time so I
don't require these three expressions
right here so I'm going to get rid of
them and here I'm going to use order by
GP ID this column now before I execute
the query look at the data that we have
here now first it displays this total
sales you know here for first and second
rows the total sales data is not
aggregated by any specific column but if
you look at the third row it's
aggregated by city column and then again
the data is not aggregated by any
specific column then we have the data
aggregated by city column then we have
data aggregated by country and city
columns because both of those columns
are now so you know this is not an you
know an order in which I want it to be
now what I want to do is I want the data
to be sorted like this first give me the
total sales where it's not grouped by
any of these specific columns in the
sense it's not aggregated by any of
these three dimensions
so in that case look at the grouping ID
column value it is 0 in this case is not
aggregated by any specific column and
then we have grouping ID column value 1
that means here the data is aggregated
by just the city column notice the city
column value
smell for all the rows where G PID
column value is 1 and then we have the
data aggregated by both the columns
country and city you know
the rows where we have G PID column
value 3 notice country and City columns
are now and finally we have the grand
total okay so when we execute this query
we should get identical results on what
we have seen on the slide so we can use
film sorting of data like that by level
of grouping now another benefit is you
can also filter by level of grouping now
here at the moment we have all the data
from the result set now for some reason
if you want only the data that is
aggregated by let's say city level then
you can filter we're grouping ID is 1 so
I can say having grouping of this equals
1 so this is going to give us the data
that is aggregated at the city level now
if you want only the data that is
aggregated you know by country and city
then probably you can use 3 so we get
the continent level data and if you want
the grand total you can use 7 so
filtering is also possible by level of
grouping and all this is possible
because by using the grouping ID
function thank you for listening and
have a great day