CODEWITHSUNDEEP
sqlsql-servert-sql
nicomp
nicomp

Reputation: 4647

Use ROLLUP with ORDER BY clause moves the Grand Total row from the bottom to the top

Here's the schema: enter image description here

This works:

This works

However, when I add an ORDER BY clause, the Grand Total row moves from the bottom. I understand the first example has the rows sorted by Genre but I wonder why adding the clause breaks it. enter image description here

Upvotes: 2

Views: 5197

Answers (3)

Uttam
Uttam

Reputation: 736

The below should work by using Grouping() function in ORDER BY clause:

USE OtherFunctionsYouShouldKnow2060
SELECT COALESCE (Genre, 'GrandTotal')
    , Sum(TotalUnitsSold) as [Total Units Sold]
FROM tAlbum
GROUP BY ROLLUP(Genre)
ORDER BY GROUPING(Genre),Genre;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You can use multiple keys for the order by:

select coalesce(Genre, 'Grand Total'), sum(TotalUnits) as total_units_sold
from album 
group by Rollup (Genre) 
order by (case when genre is null then 1 else 2 end) desc,
         genre;

Upvotes: 5

Avi
Avi

Reputation: 1845

If you see you are ordering by Column itself, and that value will remain null when you use roll up. 

create table Album( Genre varchar(10), totalunits int)

insert into Album values ('Pop', 100), 
('Pop', 200), 
('Pop', 300), 
('Rock', 500), 
('Rock', 1000), 
('Rock', 1500)

If you run this query, you get this output. 

select  Genre, sum(TotalUnits) [Total Units Sold]  from album 
group by Rollup(Genre) 
order by Genre

Output: 

Genre   Total Units Sold
NULL    3600
Pop     600
Rock    3000

So basically null will be ordered first and then others. Since, roll up will give you total for the group with null column value. Since you have used Grand total, and ordered by just genre it will order by null first. 

select  Coalesce(Genre, 'Total') GenreNew, sum(TotalUnits) [Total Units Sold]  from album 
group by Rollup(Genre) 
order by Coalesce(Genre, 'Total')

or 

select  Coalesce(Genre, 'Total') GenreNew, sum(TotalUnits) [Total Units Sold]  from 
album 
group by Rollup(Genre) 
order by GenreNew

Output: 

 GenreNew   Total Units Sold
 Pop        600
 Rock       3000
 Total      3600

Upvotes: 0

Related Questions