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:
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
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).
It's possible with GROUPING SETS
, try this:
SELECT Name,datee,SUM (val)
FROM @t
GROUP BY
GROUPING SETS((NAME ,datee), ())