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:

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).

sql online is here

回答1:

It's possible with GROUPING SETS, try this:

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

SQL Fiddle

回答2:

It is also possible with ROLLUP():

SELECT
  Name,
  datee,
  SUM (val) 
FROM @t 
GROUP BY 
  ROLLUP((NAME, datee))
;

WITH ROLLUP, as well as WITH CUBE, are non-standard and deprecated. (See Non-ISO Compliant Syntax in the GROUP BY manual.)

It should be noted that ROLLUP() isn't supported in compatibility level under 90 in SQL Server 2005 or under 100 in SQL Server 2008+, while GROUPING SETS() is.

回答3:

If you just want the final total, can't you just use a UNION ALL:

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

See SQL Fiddle with Demo

Or you can use a WHERE clause to filter the rows with the null values:

select name, 
  datee, 
  total
from
(
  SELECT  Name,datee,SUM (val) total
  from @t 
  GROUP BY NAME, datee with rollup
) src
where datee is not null
or
(
  name is null 
  and datee is null
)

See SQL Fiddle with Demo

The result is:

|   NAME |      DATEE | COLUMN_2 |
----------------------------------
|      a | 2012-01-02 |      200 |
|      a | 2012-01-03 |      100 |
|      a | 2012-01-05 |      100 |
|      b | 2012-01-06 |      200 |
|      b | 2012-01-07 |      200 |
|      d | 2012-01-07 |      400 |
|      e | 2012-01-09 |      500 |
|      f | 2012-01-12 |      600 |
| (null) |     (null) |     2300 |

回答4:

You can use this query :

SELECT  *
FROM    ( SELECT    Name ,
                    datee ,
                    SUM(val) summ
          FROM      @t
          GROUP BY  NAME ,
                    datee
                    WITH ROLLUP
        ) A
WHERE   ( datee IS NOT NULL
          OR ( datee IS NULL
               AND name IS NULL
             )
        )