Using SQL Server ROLL UP to Get Similar Results of COMPUTE BY


By:   |   Updated: 2020-09-10   |   Comments (3)   |   Related: More > T-SQL


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:

result set

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:

result set

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:

result set

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
result set
Next Steps


Last Updated: 2020-09-10


get scripts

next tip button



About the author
MSSQLTips author Mircea Dragan Mircea Dragan is a Senior Computer Scientist with a strong mathematical background with over 18 years of hands-on experience.

View all my tips





Comments For This Article




Sunday, September 13, 2020 - 5:49:35 PM - Mircea Dragan Back To Top (86466)
@Joe Celko you can change the code to meet your requirements. I just showed how a direct replacement of COMPUTE BY clause looks like by using ROLL UP instead, and how you can take advantage of ROLL UP in some scenarios

Friday, September 11, 2020 - 3:37:47 PM - Joe Celko Back To Top (86463)
you forgotten an important fact about how to use this feature. Some NULLs in the result set are created by the operators, but not all NULLs are created this way. It is possible for the original data have "real" NULLs instead of "generated" NULLs. To handle this. We have the GROUPING (<column reference>) function, which returns one if an all was generated for that column. There's also a multicolumn version of this which constructs a binary number, but I'll skip it for now.

For example, consider this query:

SELECT
CASE GROUPING (department_name)
WHEN 1 THEN 'department total'
ELSE department_name END AS dept_name,
CASE GROUPING (job_title)
WHEN 1 THEN 'job_title total'
ELSE job_title END AS job_title,
SUM(salary_amt) AS salary_amt_tot
FROM Personnel
GROUP BY ROLLUP (department_name, job_title);

Friday, September 11, 2020 - 9:40:29 AM - Jim Staples Back To Top (86459)
Alternatively you can use grouping sets:

SELECT Country
, [State]
, City
, SUM([Population (in Millions)]) AS [Population (in Millions)]
FROM #Population
GROUP BY grouping SETS((Country, [State], City), (Country, State))
order by country, [state], city desc


download





Recommended Reading

Cursor in SQL Server

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

How to use @@ROWCOUNT in SQL Server

SQL Server Loop through Table Rows without Cursor








get free sql tips
agree to terms


Learn more about SQL Server tools