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

By:   |   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)GOINSERT 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 PopulationORDER BY Country, [State], CityCOMPUTE 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],CityWITH 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) xwhere x.Country is not null and x.State is not null and city is not nullunion allselect 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) ywhere y.Country is not null and y.State is not null and city is nullorder 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) ywhere y.Country is not null and y.State is not null and city is nullorder by country, [state], city desc
```
##### Next Steps
• Try to get a closer result to what COMPUTE BY produces, by using only one SELECT
• References

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

View all my tips

 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 () 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_totFROM PersonnelGROUP 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 #PopulationGROUP BY grouping SETS((Country, [State], City), (Country, State))order by country, [state], city desc