SQL Server ROLLUP and CUBE. Is it Same

Hey Folks,

You might have heard about this feature which was introduced earlier in the SQL Server. This was introduced with the syntax name WITH ROLLUP and WITH CUBE which is Non-Ansi standard keywords, and being deprecated. Since it still works for now, it soon will be removed from the future version of SQL Server. So it’s better, if you stop using this.

Since then, with the release of SQL Server 2008; these keywords has been modified and being replaced by ROLLUP and CUBE extensions to GROUP BY which generate OLAP-type summaries of the data with subtotals and totals.

  • The ROLLUP and CUBE aggregate functions generate subtotals and grand totals as separate rows, and supply a null in the GROUP BY column to indicate the grand total.
  • ROLLUP generates subtotal and total rows for the GROUP BY columns.
  • CUBE extends the capabilities by generating subtotal rows for every GROUP BY column.
  • ROLLUP and CUBE queries also automatically generate a grand total row.
  • A special GROUPING () function is true when the row is a subtotal or grand total row for the group.

I think so, by now you should be very much familiar with GROUPING () function; if that might not be the case with you, then you have an option to get the idea about GROUPING () function, for which I have posted a blog recently. So you can follow up via link;

We will discuss each one of them separately and will see where actually these both (ROLLUP and CUBE) differs from each other:

ROLLUP () Function:

The ROLLUP option, placed after the GROUP BY clause, instructs SQL Server to generate an additional total row. This will be clear to you if you see this example:

Let see the table that I have used in the example:

1_SQL_Server_ROLLUP_and_CUBE_Isit_Same

So, now the GROUPING () function is being used by a CASE expression to convert the total row to something understandable.

If I want to display the “Class” and “Salary” with the “Lname”, then we can write the query:

SELECT LName,GROUPING(Class) AS 'Grouping',
Class,
CASE GROUPING(Class)
WHEN 0 THEN Class
WHEN 1 THEN 'All Classes'
END AS ClassRollup,
SUM(Salary) AS Salary
FROM Students   
GROUP BY ROLLUP(Class)

OOPS!!! It displays an error-

Msg 8120, Level 16, State 1, Line 1

Column ‘Students.S_Id’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 

   

Well can you guess it the error? If not, then I will suggest you to look at my query once again, is it ok enough to display the “LName” attribute?

Still, if you won’t find any answer, then you don’t have to bother much about it because I have posted a blog on this error as “Aggravating Queries”, which will guide you about some aspects of GROUP BY clause. So just browse this link;

So, now I think you will understand what wrong I had did in my previous query and had modified it as shown below, inserting the non-aggregated column into the GROUP B Y clause:

SELECT LName,GROUPING(Class) AS 'Grouping',
Class,
CASE GROUPING(Class)
WHEN 0 THEN Class
WHEN 1 THEN 'All Classes'
END AS ClassRollup,
SUM(Salary) AS Salary
FROM Students
GROUP BY ROLLUP(Class,LName)

The result can be seen as:

2_SQL_Server_ROLLUP_and_CUBE_Isit_Same

Here, you can see that ROLLUP has added new row at line 4, 8, 12 and 13.

CUBE () Function:

A CUBE query is the next logical progression beyond a rollup query. It adds subtotals for every possible grouping in a multidimensional manner, just like Analysis Services.

This will be clear to you if you see this example in which the CUBE query has subtotals for each “Class” and “Lname”:

SELECT
CASE GROUPING(Class)
WHEN 0 THEN Class
WHEN 1 THEN 'All Classes'
END AS Class,
CASE GROUPING(LName)
WHEN 0 THEN LName
WHEN 1 THEN 'All Names'
END AS LName,
SUM(Salary) AS Salary
FROM Students
GROUP BY CUBE(LName,Class)

The result can be seen as:

3_SQL_Server_ROLLUP_and_CUBE_Isit_Same

The row which has highlighted above is an auto-generated row which has GROUPING value 1 in both the cases, so it displays all and sums up the result.

Hence, this was all about ROLLUP and CUBE in SQL Server.

Hope you got it understood well 🙂

And also comments on this!!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

   

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

2 Comments on “SQL Server ROLLUP and CUBE. Is it Same”

  1. I have the following table:

    FormKey Flag1 Flag2 Flag3
    ———- —— —— ——
    1 130 132 129
    1 130 130 130
    1 130 NULL NULL
    2 132 NULL NULL
    2 129 130 NULL
    2 NULL 129 NULL

    I would like to write a query/tsql script to get the following result:
    (No cursor, no complicated loops and no multiple record creation of the same record, this table is huge like to make it as fast as possible)

    FormKey Flag1 Flag2 Flag3
    ———- —— —— ——
    1 130 132 135
    2 132 135 NULL

    1. The Group By key is FormKey.
    2. If the column has at least one record value as 132 then the final or rollup value should be 132 (all other value should be ignored).
    3. If the column has all the values are 130 then the final or rollup value should be 130.
    4. If the column has all the values are NULL then the final or rollup value should be NULL.
    5. If the column has the values like 130,129, NULL and other than 132 the final or rollup value should be 135.

    I need this ASAP.

    Thanks in Advance for all your help and review.

    1. ;with cte as
      (
      SELECT FormKey,Flag1, Flag2, Flag3,
      (SELECT max(case when v = 132 then 132 else 0 end) as v
      FROM (VALUES (Flag1), (Flag2), (Flag3)) AS value(v)) as [rule_132],
      (SELECT sum(case when v = 130 then 1 else 0 end) as v
      FROM (VALUES (Flag1), (Flag2), (Flag3)) AS value(v)) as [rule_130],
      (SELECT sum(case when v is null then 1 else 0 end) as v
      FROM (VALUES (Flag1), (Flag2), (Flag3)) AS value(v)) as [rule_NULL],
      (SELECT sum(case when v 132 then 1 else 0 end) as v
      FROM (VALUES (Flag1), (Flag2), (Flag3)) AS value(v)) as [rule_135]
      FROM #tmp
      )
      select FormKey, case when rule_132 = 132 then rule_132
      when rule_130 = 3 then 130
      when rule_Null = 3 then null
      when rule_135 = 3 then 135
      end
      from cte
      group by FormKey, case when rule_132 = 132 then rule_132
      when rule_130 = 3 then 130
      when rule_Null = 3 then null
      when rule_135 = 3 then 135
      end

Leave a Reply

Your email address will not be published.