By: Mircea Dragan | Updated: 2020-09-10 | Comments (3) | Related: > TSQL
Problem
As you may know, Microsoft removed the COMPUTE BY clause for SELECT statements starting with SQL Server 2012. The ROLL UP clause, can be used as a replacement for COMPUTE BY, but does not give the same result.
In this article the author tries to find a replacement of the COMPUTE BY clause, by using the ROLL UP clause and the conclusion of his article is an open question. From what I saw the problem is still open, the comments section does not provide a valid answer to his question.
Solution
There are situations when we want to have not only some totals displayed for our data, but also some intermediate totals for each group of data. Let’s suppose we want to calculate the population statistics based on a country, and for each country we want to compute population for each state. Another example, is if we want to calculate the statistics of a particular product in a large store chain, like Walmart. We need these results grouped by state and city, showing the quantity of each product we have in each state and city. It would be perfect if we can get these results using as few statements as possible.
In earlier versions of SQL Server (2008R2 and earlier) the SELECT statement had the COMPUTE BY clause, which provided this type of information, but no longer exists starting in SQL 2012.
To illustrate how this works, I will use a similar table structure and data the author uses in his article, but with a FLOAT data type instead of INT.
CREATE TABLE Population
(
Country VARCHAR(100),
[State] VARCHAR(100),
City VARCHAR(100),
[Population (in Millions)] FLOAT
)
GO
INSERT INTO Population VALUES('USA', 'Texas','San Antonio',1.5)
INSERT INTO Population VALUES('USA', 'Texas','Fort Worth',0.9)
INSERT INTO Population VALUES('USA', 'Texas','Huston',2.3)
INSERT INTO Population VALUES('USA', 'California','Los Angeles',3.9)
INSERT INTO Population VALUES('USA', 'New York','New York',8.3)
INSERT INTO Population VALUES('USA', 'California','San Diego',1.4)
INSERT INTO Population VALUES('India', 'Karnataka','Bangalore',9.5)
INSERT INTO Population VALUES('India', 'Karnataka','Belur',2.5)
INSERT INTO Population VALUES('India', 'Karnataka','Manipal',1.5)
INSERT INTO Population VALUES('India', 'Delhi','East Delhi',9 )
INSERT INTO Population VALUES('India', 'Delhi','South Delhi',8 )
INSERT INTO Population VALUES('India', 'Delhi','North Delhi',5.5)
INSERT INTO Population VALUES('India', 'Delhi','West Delhi',7.5)
INSERT INTO Population VALUES('India', 'Maharastra','Mumbai',30)
INSERT INTO Population VALUES('India', 'Maharastra','Pune',20)
INSERT INTO Population VALUES('India', 'Maharastra','Nagpur',11 )
INSERT INTO Population VALUES('India', 'Maharastra','Nashik',6.5)
GO
As we see, we have data from two countries, and for each country we have three states with some cities (population for USA cities is taken from Wikipedia).
Once we have the table created, we can use the following SELECT statement with the COMPUTE BY clause:
SELECT Country, [State], City, [Population (in Millions)] FROM Population
ORDER BY Country, [State], City
COMPUTE SUM([Population (in Millions)]) BY Country,[State]
This statement gives the following result:
As we see, for each group of country and state (these criteria are specified in the COMPUTE BY clause) we get another result set with the corresponding sum of population (this is what we requested in COMPUTE BY).
Starting with SQL Server 2012 Microsoft dropped the COMPUTE BY clause and kept only the ROLLUP clause. This clause does not provide the same result set as COMPUTE BY. The same SELECT statement as above but with ROLLUP instead.
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)] FROM Population
GROUP BY Country,[State],City
WITH ROLLUP
This the result:
As we see, we get only one result set, but it looks completely different compared with what COMPUTE BY produces. This result is a better candidate compared to the one produced by COMPUTE BY to be used in reports for example, where we need to compute such partial results (SUM in this example) for each particular group of data.
The ROLLUP clause gives even more information compared to COMPUTE BY, like sum per country (lines 15 and 25) and sum for the whole SELECT (line 26).
If we want to get a result as close as possible to what COMPUTE BY produces, we can use the following SELECT.
select *
from
(
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)] FROM Population
GROUP BY Country,[State],City
WITH ROLLUP
) x
where x.Country is not null and x.State is not null and city is not null
union all
select country, [state], ' ' as city, [Population (in Millions)]
from
(
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)] FROM Population
GROUP BY Country,[State],City
WITH ROLLUP
) y
where y.Country is not null and y.State is not null and city is null
order by country, [state], city desc
If we run this query, we will get the following result:
If we want only the sums per country and state, we can use the following SELECT:
select country, [state], ' ' as city, [Population (in Millions)]
from
(
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)] FROM Population
GROUP BY Country,[State],City
WITH ROLLUP
) y
where y.Country is not null and y.State is not null and city is null
order by country, [state], city desc
Next Steps
- Try to get a closer result to what COMPUTE BY produces, by using only one SELECT
- References
About the author
Mircea Dragan is a Senior Computer Scientist with a strong mathematical background with over 18 years of hands-on experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-09-10
