分組統計並且累加的問題

分組統計並且累加的問題

　　問：說有張表 Table (Date, Name, Income).
我想按Name和Date進行月份分組合計Income(不同的姓名和月份要區分出來)，並且增加一個欄位Cum，顯示各月的Income的累加(當然月份是按升序排列)。Date欄位是日期型，格式:mm/dd/yyyy,這個查詢怎麼寫?謝謝!

　　答：如果沒猜錯的話你的原意，Income
和Cum是兩個欄位，結果集應該是:

　　Month Name Income/Month Cum

Cum應該顯示逐月累加的值，Income/Month顯示的是各月的小計值。

Declare @t
Table(Date datetime,name varchar(50),Income int)
Insert @t Select '2006-5-9','A',200
Union all Select '2006-5-19','B',300
Union all Select '2006-6-19','A',300
Union all Select '2006-6-29','B',300

--group by convert(varchar(7) , date , 121)

/*
Select sum(Income) ,convert(varchar(7) , Date , 121) , Name
from @t  group by name, convert(varchar(7) , Date
, 121)   with rollup
order by  convert(varchar(7) , Date , 121)
*/

SELECT   convert(varchar(7) ,
Date , 121) as date1,
     
--   CASE WHEN (GROUPING(
convert(varchar(7) ,Date,121) ) = 1) THEN 'ALL'
       
--  
ELSE   convert(varchar(7) , Date
, 121) --ISNULL(convert(varchar(7) , Date , 121), 'UNKNOWN')
--WHEN (GROUPING( convert(varchar(7) ,Date,121) ) <> 1) THEN
convert(varchar(7) ,Date,121)
--        
END AS Date2,
      
CASE WHEN (GROUPING(name) = 1) THEN 'ALL'
           
ELSE ISNULL(name, 'UNKNOWN')
      
END AS Name,
      
SUM(Income) AS QtySum
FROM @t
--where 
--date1 is not null
GROUP BY convert(varchar(7) , Date , 121), name WITH ROLLUP
order by convert(varchar(7) , Date , 121) asc

-------
/*

Month 
Name                                              
Income/Month
CUM        
------ --------------------------------------------------
------------ -----------
200605
A                                                 
200         
200
200606
A                                                 
300         
500
200605
B                                                 
300         
300
200606
B                                                 
300         
600
*/