1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

CUBE function in SQL Server

Discussion in 'SQL Server' started by mail.yuva, Feb 9, 2011.

  1. mail.yuva

    mail.yuva New Member

    Joined:
    Jan 10, 2011
    Messages:
    44
    Likes Received:
    9
    Trophy Points:
    0
    Occupation:
    Software Developer
    Location:
    Coimbatore
    CUBE generates a result set that represents aggregates for all combinations of values in the selected columns. CUBE automatically generates a summation rollup to the top level of a GROUP BY.

    Lets us look one Example.



    Code:
    Create table #TempTable(Items varchar(30),
                                          Model varchar(10),
                                          Price float,
                                          Quantity int)
     
    Insert into #TempTable values('Furniture','Chair',100,1)
    Insert into #TempTable values('Furniture','Chair',200,1)
    Insert into #TempTable values('Furniture','Table',300,2)
    Insert into #TempTable values('Electronic Item','Pendrive',450,2)
    Insert into #TempTable values('Electronic Item','Pendrive',150,1);
     
    Select 
      Case 
            When grouping(Items) = 1 then 'All Items'
            Else Items End 'Item List',
      Case
            When grouping(Model) = 1 then 'All Model'
            Else Model End Models,
      Sum(Price*Quantity) as Total
    From #TempTable
    Group by Items,
                  Model
    With Cube
     
    Drop table #TempTable
    
     
    suraj2011 and Mittu like this.

Share This Page