問題描述
與 SQL 服務器匯總匯總 ‑ 但只有最後一個摘要？ (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
)
)
(by Royi Namir、Ivan Golović、Andriy M、Taryn、Shyam Prasad)