Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Difference between cube and rollup in SQL Server

Suggested Videos
Part 101 - Grouping sets in SQL Server
Part 102 - Rollup in SQL Server
Part 103 - Cube in SQL Server



In this video we will discuss the difference between cube and rollup in SQL Server.



CUBE generates a result set that shows aggregates for all combinations of values in the selected columns, where as ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

Let us understand this difference with an example. Consider the following Sales table.
difference between cube and rollup in SQL Server

SQL Script to create and populate Sales table
Create table Sales
(
    Id int primary key identity,
    Continent nvarchar(50),
    Country nvarchar(50),
    City nvarchar(50),
    SaleAmount int
)
Go

Insert into Sales values('Asia','India','Bangalore',1000)
Insert into Sales values('Asia','India','Chennai',2000)
Insert into Sales values('Asia','Japan','Tokyo',4000)
Insert into Sales values('Asia','Japan','Hiroshima',5000)
Insert into Sales values('Europe','United Kingdom','London',1000)
Insert into Sales values('Europe','United Kingdom','Manchester',2000)
Insert into Sales values('Europe','France','Paris',4000)
Insert into Sales values('Europe','France','Cannes',5000)
Go

ROLLUP(Continent, Country, City) produces Sum of Salary for the following hierarchy
Continent, Country, City
Continent, Country, 
Continent
()

CUBE(Continent, Country, City) produces Sum of Salary for all the following column combinations
Continent, Country, City
Continent, Country, 
Continent, City
Continent
Country, City
Country,
City
()

SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY ROLLUP(Continent, Country, City)

difference between cube and rollup in sql server 2005

SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY CUBE(Continent, Country, City)

difference between cube and rollup in sql server 2008

You won't see any difference when you use ROLLUP and CUBE on a single column. Both the following queries produces the same output.

SELECT Continent, Sum(SaleAmount) AS TotalSales
FROM Sales
GROUP BY ROLLUP(Continent)

-- OR

SELECT Continent, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY CUBE(Continent)

sql server rollup vs cube

5 comments:

  1. Hi Venkat,

    How to create a tournament fixture with the following table.

    TEAM
    ID TEAM_NAME
    1 INDIA
    2 PAKISTAN
    3 AUSTRALIA
    4 SOUTH AFRICA
    5 NEW ZEALAND

    I need output to be like this.

    Tournament Fixture
    MATCH NO TEAM A TEAM B
    1 IND PAK
    2 IND AUS
    3 IND SA
    4 IND NZ
    5 PAK AUS
    6 PAK SA
    7 PAK NZ
    8 AUS SA
    9 AUS NZ
    10 SA NZ

    ReplyDelete
    Replies
    1. WE can use LeftJoin
      select A.Team_Name,B.Team_Name from Team A Leftjoin Team B on A.Team_Name = B.Team_Name

      Delete
  2. Create Table Teams
    (
    Id Int,
    TeamName NVARCHAR(100)
    )
    Insert Into Teams Values(1,'INDIA')
    Insert Into Teams Values(2,'PAKISTAN')
    Insert Into Teams Values(3,'AUSTRALIA')
    Insert Into Teams Values(4,'SOUTH AFRICA')
    Insert Into Teams Values(5,'NEW ZEALAND')

    Select ROW_NUMBER() OVER(ORDER BY B.Id) AS 'MatchNo.', LEFT(B.TeamName, 3) AS 'Team A', LEFT(A.TeamName, 3) AS 'Team B' from Teams A Cross Join Teams B WHERE A.ID <> B.Id

    If you want data in result like SA, NZ then your should use team code(County code) column as well in the table

    Regards,
    Gautam
    7411885591

    ReplyDelete
  3. Hi Gautam,

    Thanks for your reply, But each team will play with other team only once. It's kind of knockout tournament.

    Thanks
    Baskar M

    ReplyDelete
    Replies
    1. Hi Bhaskar,
      We just need to do small change in Gautham's solution to get the desired solution.
      Please refer to my solution.

      Select ROW_NUMBER() OVER(ORDER BY B.Id) AS 'MatchNo.', LEFT(B.TeamName, 3) AS 'Team A', LEFT(A.TeamName, 3) AS 'Team B'
      from #Teams A Cross Join #Teams B --WHERE A.ID <> B.Id --AND B.Id <> A.Id
      where b.id < a.Id

      Delete

It would be great if you can help share these free resources