group by clause with rollup

I'm trying to use group by with rollup clause within sql server 2005 but I'm having some problem.

This is a simple dump

create table group_roll (
id int identity,
id_name int,
fname varchar(50),
surname varchar(50),
qty int
)

go
insert into group_roll (id_name,fname,surname,qty) values (1,'john','smith',10)
insert into group_roll (id_name,fname,surname,qty) values (1,'john','smith',30)
insert into group_roll (id_name,fname,surname,qty) values (2,'frank','white',5)
insert into group_roll (id_name,fname,surname,qty) values (1,'john','smith',8)
insert into group_roll (id_name,fname,surname,qty) values (2,'frank','white',10)
insert into group_roll (id_name,fname,surname,qty) values (3,'rick','black',10)
go

If I run this simple query

select id_name,fname,surname,sum(qty) as tot
from group_roll
group by id_name,fname,surname

I get

1   john    smith   48
2   frank   white   15
3   rick    black   10

I'd like to have

1   john    smith   48
2   frank   white   15
3   rick    black   10
Total            开发者_如何学Go    73

This is what I've tried to reach my goal

select 
case when grouping(id_name) = 1 then 'My total' else cast(id_name as char) end as Name_id ,
fname,surname,sum(qty) as tot
from group_roll
group by id_name,fname,surname
with rollup
order by case when id_name is null then 1 else 0 end, tot desc

but my result is

1                               john    smith   48
1                               john    NULL    48
1                               NULL    NULL    48
2                               frank   white   15
2                               frank   NULL    15
2                               NULL    NULL    15
3                               rick    black   10
3                               rick    NULL    10
3                               NULL    NULL    10
My total                         NULL   NULL   73

Where is my mistake?

EDIT. I could solve my problem making

select * from (
select cast(id_name as char) as id_name,fname,surname,sum(qty) as tot
from group_roll
group by id_name,fname,surname 
union
select 'Total',null,null,sum(qty) from group_roll ) as t
order by case when id_name = 'Total' then 1 else 0 end,tot desc

but I'd like to understand if rollup can solve my problem.

You cannot do it within the statement itself, however you can filter the ROLLUP set excluding the intermediate rollups, i.e. where any one but not all rows are being grouped:

select
    case when grouping(id_name) = 1 then 'My total' else cast(id_name as char) end as Name_id,
    fname,
    surname,
    sum(qty) as tot
from group_roll
group by id_name, fname, surname
with rollup
having grouping(id_name) + grouping(fname) + grouping(surname) in (0 , 3)

Or similar to your solution but referencing the original query;

;with T as (
    select cast(id_name as varchar(128)) as id_name,fname,surname,sum(qty) as tot
    from group_roll
    group by id_name,fname,surname
) select * from T union all select 'Total:',null,null, SUM(tot) from T

FWIW SQL 2008 allows;

select
    case when grouping(id_name) = 1 then 'My total' else cast(id_name as char) end as Name_id,
    fname,
    surname,
    sum(qty) as tot
from group_roll
group by grouping sets((id_name, fname, surname), ())