Sum with SQL server RollUP - but only last summary?

Question

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

Solution 1

It's possible with GROUPING SETS, try this:

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

SQL Fiddle

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

Solution 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 |

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

View more solutions

Comments

[Royi Namir]

FYI @t is the result of a query. ( it doesnt matter) so ill have to write it twice. I did tried to do it with CTE but : i.stack.imgur.com/hYhOk.jpg

[Taryn]

@RoyiNamir Well the second one, should work for that since there is no union all

[Ivan Golović]

@RoyiNamir You're welcome, it's a standard TSQL feature, but it's not used that often (I guess) so people don't always know about it :)

[Ivan Golović]

@RoyiNamir I'm sure you'll find resources about it online, I read about it in a book (although I can't really remember using it).

[Martin Smith]

@RoyiNamir - The best explanation I have come across is by Itzik Ben Gan part 1 and part 2

[Ivan Golović]

You can also find some examples and explanations in 'MCTS Self-Paced Training Kit (Exam 70-433): Microsoft® SQL Server® 2008 Database Development', but it's a printed book.

[Royi Namir]

Ivan , it seems that from now on I can forget about group by a,b,c and start doing group by grouping sets(a,b,c) as a preperation for future sum's ( if i ever need any)......right ?

[Ivan Golović]

@RoyiNamir I would use GROUPING SETS only when really needed. If ordinary GROUP BY satisfies your requirements, I would stick to it. I don't know if there is any performance difference between GROUP BY GROUPING SETS and GROUP BY col1, col2... It depends on particular situation but I would tend do things the simplest way possible.