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:

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

score 41 · Accepted Answer · answered Jan 23 '13 at 11:27

41

It's possible with GROUPING SETS, try this:

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

SQL Fiddle

answered Jan 23 '13 at 11:27

Ivan Golović

8,732
3
25
31

@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ć Jan 23 '13 at 11:38
@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). – Ivan Golović Jan 23 '13 at 11:41
3

@RoyiNamir - The best explanation I have come across is by Itzik Ben Gan [part 1](http://www.sqlmag.com/article/tsql3/grouping-sets-part-1-96805) and [part 2](http://www.sqlmag.com/article/tsql3/grouping-sets-part-2-97007) – Martin Smith Jan 23 '13 at 11:42
2

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. – Ivan Golović Jan 23 '13 at 11:44
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 ? – Royi Namir Jan 24 '13 at 06:45
@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. – Ivan Golović Jan 24 '13 at 07:00

Andriy M · Answer 2 · 2013-01-23T14:16:01.220

12

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.

edited Jan 23 '13 at 14:16

answered Jan 23 '13 at 12:36

Andriy M

76,112
17
94
154

Taryn · Answer 3 · 2013-01-23T11:26:08.750

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 |

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 : http://i.stack.imgur.com/hYhOk.jpg — Royi Namir, Jan 23 '13 at 11:30
@RoyiNamir Well the second one, should work for that since there is no union all — Taryn, Jan 23 '13 at 11:31

score 1 · Answer 4 · edited Dec 01 '13 at 08:30

1

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

edited Dec 01 '13 at 08:30

Ardalan Shahgholi

11,967
21
108
144

answered Aug 01 '13 at 07:24

Shyam Prasad

974
1
6
17

Sum with SQL server RollUP - but only last summary?

4 Answers4

Linked