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

this is part one zero three of sequel
server tutorial in this video we'll
discuss the cube function in sequel
server cube in sequel server produces
the result set by generating all
possible combinations of columns
specified in group I cube let's
understand this with an example we have
this employee's table here now we want
to write a query that's going to group
salaries by all the possible
combinations of these two columns that
is gender and country so there are two
columns by which we want to group salary
so what are the different possible
combinations of these two columns the
first combination is we want to group
salary by both the columns by both
gender and country that's the first
combination we want to group salaries
just by gender that's the second
combination group salaries just by
country third combination and we want
the grand total in this case the salary
is not grouped by any specific column so
that's the fourth combination the result
set of our query should look like this
if you look at the results that notice
the first three rows we have this
Hillary group both by country and female
gender and then we have the salary group
just by female gender then we have
salary grouped by country and male
gender and then we have cell regrouped
just by male gender notice country
column is now and then we have the grand
total row where both the country and
gender columns are null and then the
last three rows we have salaries group
just by the country column in this case
the gender column is now let's see how
to achieve this using grew by cube
function so let's flip to sequel so
management studio within our result set
we want the country column and then
gender column and then sum of salary
column let's give this column and ideas
let's call this total salary from
employees table and we want to group by
now if we say group by country comma
gender then within our result set we are
going to get the salary grouped just by
both the columns country
gender we don't have the other
combinations that is salary grouped just
by country column salary group just by
gender column and the grand total now if
we want all the different possible
combinations of these two columns then
we can use the cube function in secret
server so now we can say group I cube
and to the cube function we pass the
comma-separated list of columns by which
we want to group salaries so in this
case you know by country and gender so
when I execute this notice now we get
twelve rows and with no results that we
have all the possible combinations of
these two columns that is country and
gender so notice the first three rows we
have salary grouped by both the columns
country and female gender and then the
fourth row salary group just by female
gender notice country column is now and
then we have salaries grouped by country
and male gender salary grouped just by
male gender and then the grand total
finally we have salaries group just by
the country column now we can also
rewrite this query using a slightly
different syntax so we can either say
grew by cube function and that function
we pass the comma separated list of
columns or we can say group by these two
columns country come agenda with cube so
when we execute these two queries we
should get identical results so first of
all look at that twelve rows from both
the result sets and the output also
should be identical now we can also
achieve this using grouping sets let's
look at that in action so let me make a
copy of this query so select country
gender sum of salary as total salary
from employees group by and I'm going to
use grouping sets grouping sets and then
we specify our groups here so
first we want salaries grouped both by
country and gender and then we want this
eldest grouped just by country and then
salaries grouped just by gender and then
the grand total so let's so that's our
first grouping that's our second group
that's our third group and then finally
grand total so when we execute this we
should get the same result look at that
you know it's identical to what we have
got using this group I cube query now
again this is equivalent to writing
different group by queries and then
using Union all let's look at that in
action so I can also say I want let's
actually make a copy of this query so we
want those three columns from employee
stable grew by country comma gender so
this is going to give us salaries
grouped by both the columns and then we
can use Union all and we can say okay
give me country comm agenda so gender is
going to be null in this case so this is
going to give us salaries grouped by
both the columns and then we want
salaries grouped just by country so
select country for gender it's going to
be null from employees group just by
country column and then we can apply the
Union all operator and then we want
salaries grouped just by gender in this
case country column is going to be now
we want to select gender from employees
table and we want the salary to be
grouped by gender and then finally we
want the grand total for grand total
both the columns are going to be null
that is both the country and gender
columns and we don't want to use grew by
because this is going to give us the
grand total
now when we execute this in now we
should get the same result as of the
above queries so first of all notice
we've got two twelve rows but one thing
to notice here is that the ordering of
the rows is slightly different if you
want to control the ordering use order
by okay so if you look at the
combinations we have got using this
Union all it's the same as the result of
the other queries so we have first a
salaries grouped by country and by
gender that is the first six rows
salaries grouped just by country notice
the gender column is now and then we
have salaries grouped by just gender in
this case country column is now and then
the grand total so we have different
ways of achieving the same result but
the best way to achieve this is by using
grew by cube function so here you know
the first query we are using cube with
grew by the second queries using
grouping sets and here we are using
Union all to achieve the same thing
thank you for listening and have a great
day