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

this is part one zero five of sequence
over tutorial in this video we'll
discuss grouping function in sequel
server this is continuation to part 104
so please watch part 104 before
proceeding
what is grouping function grouping
function indicates whether the column in
a group by list is aggregated or not
grouping returns one for aggregated or
zero for not aggregated in the result
set let's understand what we mean by the
statement with an example here we have
total sales aggregated across these
three columns continent country and city
and then we have three other columns
here so basically these three columns
are going to tell us whether if this
total sales value is aggregated across
any of these columns so if you look at
the first row this total sales figure is
not aggregated by any specific column so
notice GP underscore continent GP n
disco country and GP n disco city all of
them are 0 indicating that data is not
aggregated for any of those columns
whereas if you look at this third row
look at that city is null so this
subtotal here 3,000 this total sales is
for all cities within India country that
is present in Asia or continent so this
figure is aggregated across city column
so notice GP underscore city is 1
whereas the other two columns are 0 now
if you look at this row right here this
subtotal is for all cities and for all
countries in Asia continent and if you
look at the respect to GP underscore
columns here notice country and city are
1 whereas continent is 0 and if you look
at the last row here all of the column
values are null so this is the grand
total so in this case the data is
aggregated across all those three
columns notice GP underscore column
values here for all the columns are ones
so let's see how to produce this output
using this grouping function so this
function is going to return 1 if the
data is aggregated across the column
that we specify otherwise it's going to
return 0
so let's flip the sequel so management
studio so this is the same query that we
worked with in the previous video
session so when we execute this we get
the total sales grouped by continent
country and city now we also want these
three columns GP underscore continent DB
Ensco country and GP underscore city for
that we can use the grouping function
now one important thing to keep in mind
is that with this grouping function you
can only specify one column that means
here to get these three columns you will
have to use grouping function three
times so let's look at that in action
now so along with the sale amount we
want to use the grouping function and
I'm going to pass continent and let's
give it an early us GP underscore
continent and similarly we want the same
thing for country and city so grouping
off country as GP underscore country and
grouping off city as GP underscore city
so let's execute this and notice that if
data is aggregated across a column we
get one for that GP underscore column if
it's not aggregated we get zero all
right now the next obvious question that
comes to our mind is okay I know what is
grouping function but what is the use of
this grouping function in the real world
now look at this when we have you know
this total sales amount aggregated by
default in the result set it will
display null value but if you look at
from the end-user perspective null value
doesn't make any sense at all instead of
nulls don't you think it makes sense to
display a word like this all or all
cities which is more meaningful so when
you say all here you know the user can
read this as okay this total sales
amount is for all cities in India
country that's present in Asia continent
whereas if you look at all
here then this clearly says to the
end-user this is the grand total of
total sales across all cities all
countries and all continents okay so we
can use this grouping function to
achieve this now if you look at the
result here notice that you know by
whatever column we have the data
aggregated across those columns we have
null values now we want to replace those
null values with this all third so let's
see how to do that so first of all in
our result set we don't need these three
additional columns so I'm going to
remove them and we want to use the
grouping function so in order to replace
the null value with this word all I'm
going to use the case statement and
grouping function so select case when
grouping off let's take this continent
pass it to a grouping function so when
grouping of continent equals one that
means data is aggregated across that
column so if that's the case we know
that we are going to get a null value
and at that point we want to display all
instead of null LS so if it's not
grouped across that continent then what
do we want to display we want to display
the respective continent name now if you
look at the data that we have we don't
have any null values within continent
country or city columns but if we have
null values we don't want to display
null instead we want to display you know
unknown for a the continent country or
city so what I'm going to do here is use
another is null function and pass the
continent column to it so if the
continent column is now then I want to
display you know this word unknown
instead of null in the result set
and then let's end the case statement as
let's give that column in Aaliyah's we
want that column name to be continent
okay so we need to do the same thing for
country and city as
so I'm going to make a copy of that and
let's pass country so grouping of
country is equal to one then all else if
country is not again display unknown and
and we want the column name to be
country and let's do the same thing for
city
so when grouping of city is equal to one
then all else check F city column has
got a null value if that's the case
display unknown so by the way we have
unknown spelling around here let's
correct that and let's give this an
alley as of City and then we want the
total sales amount all right so let's go
ahead and execute this one now look at
that instead of nulls we have all so now
if you look at this one what is that
this subtotal is for all cities all
countries in Asia continent whereas this
is a grand total it's for all cities all
countries and all continents so much you
know better than what we had before all
right so here is that case statement
that we have just looked at now the next
obvious question that comes to our mind
is why can't I use is null function
instead of grouping function to achieve
the same result
well we can use it as long as we don't
have any null data within the sales
table let me explain what I mean so
first of all let's see when we use EES
null function instead of in our grouping
function if we get the same result I'm
actually going to make a copy of this
query so let's open another query editor
vendor now let's use EES null function
so I'm going to get rid of this big case
statement and let's use is now is null
of continent so if continent is null
then we want to display this word all
let's do the same
thank for country and city okay so if
country is now then display all if city
is now then display all so now when I
execute this one notice that okay
obviously we have to give that column an
early us so let's give it an early as as
continent and let's do the same thing
for city and country as country and
let's do it for city as well so now when
we execute this we get the same result
right so when we use is now a grouping
function we are able to achieve that now
we can use these null function to
achieve the same thing as long as our
raw data in the sales table does not
contain any null values let me explain
what I mean so at the moment if you look
at the sales table let's execute a
select query on the sales table this is
the table that we created in our
previous video session so if you look at
this continent country and City columns
we don't have null values anywhere now
let's go ahead and change you know the
city Bangalore to null and to do that
lets issue an update statement
so update sales table set City equals
null and the idea of that row is 1 so
we're ID equals 1 so now let me execute
this and now when we select the data
from this Hills table notice that for
the first row for Asia continent India
country you know city is now now when we
execute this query look at what's going
to happen since within the data itself
we have now you know it's replacing that
also with all and this is kind of
misleading so you know if we read this
from an end-user perspective it's like
for all cities within India country in
Asia continent the total sales is 1
okay now why did we get all there that's
because the raw data had a null value
and because we are using is null
function here wherever content is null
we are replacing that at all and that's
the reason why we got it there whereas
when we use this query notice that we
don't have that problem
so when city is null its displaying that
as unknown we don't have a city name
there so it's displaying unknown but not
all okay so now this is much readable
that's why grouping is very useful now
this grouping function can be used with
any of these functions rollup cube or
grouping sets so at the moment we using
roll-up when I use cube and when we have
the data aggregated across columns we
are going to get another same result and
when we use grouping sets as well so
instead of cube when we use grouping
sets again we can still use
grouping function so this is the first
group by which we want to group the
total sale amount then we want it to
group by continent country and just by
continent final a grand total so here
we're using grouping sets so now when we
execute this we still get all thank you
for listening and have a great day