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 (https://blog.sqlauthority.com)
23 Comments. Leave new
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
Hi pinal sir,
Thank you for examples.
Regard$
Chirag Satasiya
The WITH ROLLUP clause also works with my SqlServer 2008 R2.
Anyway, one more great article, thanks Pinal!
gr8…thanks sir
ROLLUP will work in 2008 R2 as well.
WITH ROLLUP has been around since 2000.
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”
hi Pinal sir
Thanks lot..for lot of example
~Rgds
Shash
Thanks a lot sir.
Thank you man perfect
Thankyou for every information you share as it is really useful.
Nice article
Nice Article…:)
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.
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.
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
Thank You
But it’s not like compute
Simple way to do compute sum;
Select columnname, count(*)
From tablename
Group by columnname
Order by columnname
Select ‘SUM’ , COUNT(columnname) from tablename
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)] FROM tblPopulation
GROUP BY Country,[State],City
WITH ROLLUP
ORDER BY 1, 2, 3, 4
“with rollup” clause is quite a bit short of the capabilities of the “compute by” clause.
The “compute by” clause allows the inclusion of detail columns while summing the detail data. The “with rollup” clause requires all detail data to be either part of the group by clause or be part of an aggregate. The “compute by” only requires the column to be in the “order by” clause. Try changing the “compute by” query below into a “with rollup” with the same result set. I suspect it can be done with some sort of query, I took a couple of quick stabs not worth posting.
select TableName=substring(object_name(a.id),1,40),
MB=a.used * (8192 / 1048576.0),
TableSubSet=substring(object_name(a.id),1,3)
from sysindexes a
inner join sysobjects b
on a.id = b.id
where indid in(0,1,255) AND (used* (8192 / 1048576.0)) >50.0
ORDER BY substring(object_name(a.id),1,3), used DESC
compute sum(a.used * (8192 / 1048576.0)) by substring(object_name(a.id),1,3)
Which returns this result set if you don’t have a pre-2012 server
TableName MB TableSubSet
—————————————- ————————————— ———–
dummy_data1 144.531250000 dum
dummy_data2 142.890625000 dum
dummy_data3 133.601562500 dum
dummy_data4 96.593750000 dum
dummy_data5 74.406250000 dum
sum
—————————————
592.023437500
TableName MB TableSubSet
—————————————- ————————————— ———–
dummy_data6 543.757812500 dum
dummy_data7 92.210937500 dum
sum
—————————————
635.968750000
how’d you be able to incorporate those summaries into crystal report or ssrs reporting?