SQL: Group By, Rollup, cube, grouping

 

   Grouping is used to identify count of distinct values in specific object. we have been applying grouping on  columns of table.

    Syntax 

•     Group by  <col_name1>, <col name2>
•     Rollup (<col_name1>, <col name2>)
•     Cube ((<col_name1>, <col name2>)
•     Grouping (<col name1>)

     We use group by to identify count or sum against distinct values of columns in table

      select col1 , col2, count(1) from table_t group by col1, col2.

here it is identified distinct value between col1 & col2 and display no of rows

Rollup   --- this function used to grouping in next level

     n --- No of columns

if we are doing rollup between 2 columns then you have need to do rollup next 3 level (n+1) level.

 

 First level --- It shows normal goruping

 2nd level   --- Grouping at distinct value of first columns

 3rd level    --  Grand total 

Cube   ---- This function used to grouping in next level

   n --- column numbers

( N*2)

so we have calculate level is 4

first level --- Shows normal grouping

2nd level -- Grouping at distinct value of first column

3rd level  -- Grouping at distinct value  of 2nd column

4th level  -- Grouping ( Grand Total)

==============================================

Testing script

create table group_test_t

( col1 number,

  col2 number)

 --- here my table has created

 -- I am going to understand grouping , gorup by ,rollup and cube function here

    --col1 has 2 distinct values and col2 has 3 distinct value

 ---step 2-- Insert data into table

   insert into group_test_t (col1,col2) values (1,1);

   insert into group_test_t (col1,col2) values (2,2);

   insert into group_test_t (col1,col2) values (2,3);

   ---step3 --- test group by function using count function now I am going find for 2 col 

   select col1,col2 ,count(1) as nu_rows from group_test_t group by col1,col2 order by 1

   --rollup always as n+1 level suppose there 2 col then leve would 3 level

   select col1,col2 ,sum(col1) as nu_rows from group_test_t group by rollup(col1,col2)  

      level 1 -- normal grouping 

      level 2-- grouping for each distict value in col1

      level 3 --- total grouping 

      col1  has value 1,  2

      col2 have value 1,2 ,3

      grouping normal 1,1 ==> 

                      2,2

                      2,3 

       level 2 grouping at distinct col1 

       1      ===> 1

       2 (2 times) ==> 2+2 =4

       level 3 --->

       1,2,3

              ==> level 2 grouping as 4 + 1 =5

              

   Now understand cube where level calculate as n*2

     select col1,col2 ,sum(col1) as nu_rows from group_test_t group by cube(col1,col2)              

       level 1-- Noraml Goruping

       level 2 --> grouping for distict value for col1

       level 3 --> grouping for distict value for col2

       level 4 --> total sub group.