Wow, that’s a mouthful. This is what I am trying to convey: When you do a Group By with Rollup, what happens is that the rolled up column shows null, and won’t necessarily be ordered at the bottom. For example:

select
    firstname
    , count(*) as total
from AdsEntered ads
join tblStaff s on s.staffid = ads.staffidgroup by firstname
with rollup
order by
total desc

This would output:

Now, I want the person with the highest total at the top, but I certainly don’t want the the total row being first. So the goal is to still sort descending, yet stick the total row at the bottom:

select
    isnull(firstname, "TOTAL") as firstname
    , count(*) as total
from AdsEntered  ads join tblStaff s on s.staffid = ads.staffid
group by firstname
with rollup
order by
case when firstname is null then 0
else 1 end  desc

This would yield:

First, I used isnull(firstname, “TOTAL”) to get rid of the ugly null. Pretty simple.

Now look at the case statement in the order by. I am saying, “when the firstname column is null, then treat it like it is 0, else treat it like it is 1. Then order it descending, so the field with the 1 will go last.” Notice I wrote when firstname is null, not when firstname = ‘TOTAL’. To check for ‘TOTAL’ you would have to apply the entire line isnull(firstname, “TOTAL”) = “TOTAL”.

Now that we got the total line in the right place, we need to order all the “0” rows descending. We do this by applying another order by on the total column.

select
    isnull(firstname, "TOTAL") as firstname
    , count(*) as total
from AdsEntered  ads join tblStaff s on s.staffid = ads.staffid
group by firstname
with rollup
order by
case when firstname is null then 0
else 1 end  desc
, total desc

Yielding:

Cool, huh?