Search code examples
sql-serversql-server-2008sumrollup

Sum with SQL server RollUP - but only last summary?

I have this query: 

DECLARE @t TABLE(NAME NVARCHAR(MAX),datee date,val money)

insert INTO @t SELECT 'a','2012-01-02',100
insert INTO @t SELECT 'a','2012-01-02',100
insert INTO @t SELECT 'a','2012-01-03',100
insert INTO @t SELECT 'a','2012-01-05',100
insert INTO @t SELECT 'b','2012-01-06',200
insert INTO @t SELECT 'b','2012-01-07',200
insert INTO @t SELECT 'd','2012-01-07',400
insert INTO @t SELECT 'e','2012-01-09',500
insert INTO @t SELECT 'f','2012-01-12',600

SELECT  Name,datee,SUM (val) 
from @t GROUP BY NAME ,datee

currently the result is:

enter image description here

BUT I need to add sum at the end. So I tried with rollup: 

 SELECT  Name,datee,SUM (val) 
    from @t GROUP BY NAME ,datee  with ROLLUP

enter image description here

BUT I only need the last sum total line. I don't need the in-report sum's

So how can get the desire result?

(I cant change the group by clause cause others need it also , I just want to add sum at the end with/without rollup).

sql online is here

Solution

  • It's possible with GROUPING SETS, try this: 

    SELECT  Name,datee,SUM (val) 
FROM    @t 
GROUP BY 
        GROUPING SETS((NAME ,datee), ())

    SQL Fiddle