SQL SERVER – Use ROLL UP Clause instead of COMPUTE BY

Note: This upgrade was test performed on development server with using bits of SQL Server 2012 RC0 (which was available at in public) when this test was performed. However, SQL Server RTM (GA on April 1) is expected to behave similarly.

I recently observed an upgrade from SQL Server 2005 to SQL Server 2012 with compatibility keeping at SQL Server 2012 (110). After upgrading the system and testing the various modules of the application, we quickly observed that few of the reports were not working. They were throwing error. When looked at carefully I noticed that it was using COMPUTE BY clause, which is deprecated in SQL Server 2012. COMPUTE BY clause is replaced by ROLL UP clause in SQL Server 2012. However there is no direct replacement of the code, user have to re-write quite a few things when using ROLL UP instead of COMPUTE BY. The primary reason is that how each of them returns results. In original code COMPUTE BY was resulting lots of result set but ROLL UP.

Here is the example of the similar code of ROLL UP and COMPUTE BY. I personally find the ROLL UP much easier than COMPUTE BY as it returns all the results in single resultset unlike the other one. Here is the quick code which I wrote to demonstrate the said behavior.

CREATE TABLE tblPopulation (
Country VARCHAR(100),
[State] VARCHAR(100),
City VARCHAR(100),
[Population (in Millions)] INT
)
GO
INSERT INTO tblPopulation VALUES('India', 'Delhi','East Delhi',9 )
INSERT INTO tblPopulation VALUES('India', 'Delhi','South Delhi',8 )
INSERT INTO tblPopulation VALUES('India', 'Delhi','North Delhi',5.5)
INSERT INTO tblPopulation VALUES('India', 'Delhi','West Delhi',7.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Bangalore',9.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Belur',2.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Manipal',1.5)
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Mumbai',30)
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Pune',20)
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Nagpur',11 )
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Nashik',6.5)
GO
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)]
FROM tblPopulation
GROUP BY Country,[State],City
WITH ROLLUP
GO
SELECT Country,[State],City, [Population (in Millions)]
FROM tblPopulation
ORDER BY Country,[State],City
COMPUTE SUM([Population (in Millions)]) BY Country,[State]--,City
GO

After writing this blog post I continuously feel that there should be some better way to do the same task. Is there any easier way to replace COMPUTE BY?

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

20 thoughts on “SQL SERVER – Use ROLL UP Clause instead of COMPUTE BY

  1. Thanks Pinal for nice article. But in the table structure, population is INT while in INSERT you are insterting FLOAT values. Though it is not affecting the ROLLUP example, can you update to either FLOAT or INT please? Thanks

  2. Hi All,

    Please note that ROLL UP Was available earlier but the point of this blog post is that COMPUTE BY will not be available in future and how you can use ROLL UP to replace COMPUTE BY.

  3. Thanks!!!!, good to know that “COMPUTE BY” should no longer be used.
    I use “ROLL UP” like this:
    SELECT Country,[State],City,
    SUM ([Population (in Millions)]) AS [Population (in Millions)]
    FROM tblPopulation
    GROUP BY ROLLUP(Country,[State],City)

    Personally, I never liked “COMPUTE BY”

  4. Hi there, I uncovered your web site by the use of Google even as searching for a affiliated subject, your world wide web web site came up, it appears fantastic. I’ve bookmarked it in my google bookmarks.

  5. In this example, the ROLLUP results show all the detail rows only because every column in the table (that is not summed) has the properties of a unique key. As a consequence, you can see all the detail Population values. If you added another row to tblPopulation:

    INSERT INTO tblPopulation VALUES(‘India’, ‘Delhi’,’West Delhi’, 4)

    so that there are now two rows with ‘India’, ‘Delhi’, ‘West Delhi’ in the table. The COMPUTE BY result set will show both rows, with Population values of 7 and 4, while the ROLLUP result set will show a combined value of 11. Thus the COMPUTE BY and ROLLUP results are not equivalent in the general sense.

    Of course, in a simple situation like this, you can always have an IDENTITY column or something include that in the ROLLUP:

    CREATE TABLE tblPopulation (
    ID INT IDENTITY(1, 1),
    Country VARCHAR(100),
    [State] VARCHAR(100),
    City VARCHAR(100),
    [Population (in Millions)] FLOAT
    )
    … inserts, including the additional one listed above …
    SELECT Country,[State],City,
    SUM ([Population (in Millions)]) AS [Population (in Millions)]
    FROM tblPopulation
    GROUP BY Country,[State],City, ID
    WITH ROLLUP

    The ID column doesn’t appear in the select list so that the results appear more like the COMPUTE BY results. To make it even more like COMPUTE BY, which doesn’t have totals for Country and all rows (the last two results shown in your original ROLLUP example), as well as a bunch of additional rows where ID is null, you have to add a HAVING condition after the group by to get rid of these:

    HAVING Country is not null AND State is not null AND (ID is not null OR City is null)

    With this HAVING condition, the results are the same as the COMPUTE BY results, except that multiple result sets become one.

    It can be difficult to write this HAVING condition for a complex query with a lot of columns and only a few with computed sums.

    Of course, in a complex query there may not be a set of columns that will be unique, and this is a requirement for ROLLUP to act like COMPUTE BY. In that case there aren’t any alternatives beyond creating a temporary table, adding an identity column to it, and using ROLLUP on that.

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #023 | SQL Server Journey with SQL Authority

  7. How to display RollUp result exact opposite…
    Means Addition first and then groupwise data
    in this example..
    Population (In Millions)
    29
    India East Delhi 9
    India North Delhi 5
    India South Delhi 8
    India West Delhi 7

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s