Roll up, Cube, Group By , Grouping , Pivot in depth

GROUP BY IN DEPTH :

MAIN TABLE : which I will be using throughout  the tutorial to explain all the things.

1) select * from sales

A)     GROUP BY EMPLOYEEID

2) select EMPID,sum(Sales) from sales group by EmpId

So employee 1 earns 55k total

 Employee 2 earn 21k total

 And employee 3 earns 44k total

 very simple.

A)      GROUP BY YEAR

SELECT Yr, SUM(Sales) AS Sales

FROM Sales

GROUP BY Yr

So in 2005 total of 27k is earned

In 2006 total of 44k is earned

And in 2007 total of 49k is earned

A)      TOTAL SUM

SELECT SUM(Sales) AS Sales

FROM Sales

 

Now how to get all the things in one shot. That is the question.

Now let’s try these two things.

àrollup

àcube

I will talk extensively regarding the difference between these two. But as of now both are fetching me the same result. But notice that the last row gives me the Total.

CUBE

select yr,sum(Sales) as yearsales

from sales

group by yr WITH cube

 

ROLLUP

SELECT Yr, SUM(Sales) AS Sales

FROM Sales

GROUP BY Yr WITH ROLLUP

 

Now both seems same. No difference at all.

YEAR IN GROUP BY WITH Rollup

SELECT EmpId, Yr, SUM(Sales) AS Sales

FROM Sales

GROUP BY EmpId, Yr WITH rollup

 

--we don’t get the what is the sales obtained in 2005,2006,2007

[ in 2005 27k, in 2006 44k and in 2007 49k]

YEAR IN GROUP BY WITH CUBE

5) SELECT EmpId, Yr, SUM(Sales) AS Sales

FROM Sales

GROUP BY EmpId, Yr WITH CUBE

 

àSo you get year wise salary: in 2005 27k, in 2006 44k and in 2007 49k.

àAnd also emp1 earns : 55k total

                     Emp2 earns:  21k total

     Emp3 earns:  44k total

àAnd also total sum    : 120000

So we got all the three queries in one shot by the above query.

But the above data doesn’t look eye catchy.  And we have to put our brain and think which one is which data.

So in this case where pivot comes into role. But what is pivot. Let me explain you in detail regarding that with an simple example.

6) Lets explore using pivot

--pivot is very simple…it looks hard,,but you need to remember 3 steps.

a)step 1 highlighted (the column names which you want)

b)second highlighted(actual data)..on which you want to do pivot

c) on what column you want to do the pivot operation highlighted yellow

select empid,[2005],[2006],[2007]

from

(

select * from sales  

)pivotdata

pivot

(

sum(Sales)

for yr in([2005],[2006],[2007])

)pivotfinal

 

So everything is cool.

àSo we need one more row (4^th  row) which will give the total sales earned in each year.

àAnd one more column (5^th column) which will give the total sales earned by particular employee id.

How to achieve that ?

àlets try this

select empid,[2005],[2006],[2007]

from

(

select SUM(Sales) as Sales, EmpId,

          Yr

      FROM Sales

      GROUP BY EmpId, Yr with rollup

     

)pivotdata

pivot

(

sum(Sales)

for yr in([2005],[2006],[2007])

)pivotfinal

ànothing much happened.

select empid,[2005],[2006],[2007]

from

(

select SUM(Sales) as Sales, EmpId,

          Yr

      FROM Sales

      GROUP BY EmpId, Yr with cube

     

)pivotdata

pivot

(

sum(Sales)

for yr in([2005],[2006],[2007])

)pivotfinal

select yr,[1],[2],[3]

from

(

select SUM(Sales) as Sales, EmpId,

          Yr

      FROM Sales

      GROUP BY EmpId, Yr with rollup

     

)pivotdata

pivot

(

sum(Sales)

for empid in([1],[2],[3])

)pivotfinal

 

--The same result you get with Cube too.

Now the null is confusing and also the result we are getting on top row. Let’s make some changes and get rid of the null

This is where grouping comes into role. Grouping is 0 for for non aggregate values. And for aggregate values it will be 1. So when we get the above result we were getting null. The 1^st row null was coming from aggregate function.

So for this grouping will be 1.

 

select yr,[1],[2],[3]

from

(

select

case when grouping(Yr)=0

THEN CAST (Yr AS CHAR(7))

  ELSE 'Final Sum Employee Wise'

  end as yr,

SUM(Sales) as Sales, EmpId

     

      FROM Sales

      GROUP BY EmpId, Yr with rollup

)pivotdata

pivot

(

sum(Sales)

for EmpId in([1],[2],[3])

)pivotfinal

 

That  is cool.

The same result we get if we use with Cube.

Now let’s explore  how to get year wise total ? I mean a column on the right hand side (total sales earned for a year).

select yr,[1],[2],[3],[Year wise total]

from

(

select

 

  case when grouping(Yr)=0

  THEN CAST (Yr AS CHAR(7))

  ELSE 'Final Sum Employee Wise'

  end as yr,

  case when grouping(EmpId)=0

  THEN CAST (EmpId AS CHAR(20))

  ELSE 'Year wise total'

  end as EmpId,

  SUM(Sales) as Sales

     

  FROM Sales

  GROUP BY EmpId, Yr with cube

)pivotdata

pivot

(

sum(Sales)

for EmpId in([1],[2],[3],[Year wise total])

)pivotfinal

 

The inner query result is as below: The actual Data 2^nd part of pivot.

Pivot part grabs the result from here.

 

<-- Same data but Changed slightlyà

select EmpId,[2005], [2006], [2007], [Final Sum Employee Wise]

from

(

select

 

  case when grouping(Yr)=0

  THEN CAST (Yr AS CHAR(7))

  ELSE 'Final Sum Employee Wise'

  end as yr,

  case when grouping(EmpId)=0

  THEN CAST (EmpId AS CHAR(20))

  ELSE 'Year wise total'

  end as EmpId,

  SUM(Sales) as Sales

     

  FROM Sales

  GROUP BY EmpId, Yr with cube

)pivotdata

pivot

(

sum(Sales)

for yr in([2005], [2006], [2007], [Final Sum Employee Wise])

)pivotfinal