组中的求和- Server 2008

Question

我的桌子看起来像下面。对于给定的客户，我必须为所有三个月添加spamt：

declare @mytable table
    (
        MNTH CHAR (7),
        BR VARCHAR (4),
        CUSTNO varchar (10),
        SPAMT DECIMAL(10,2)
    )

INSERT INTO @mytable
    SELECT '2016-09', '0001', '4152', 256.25 UNION ALL 
    SELECT '2016-10', '0001', '4152', 856.25 UNION ALL 
    SELECT '2016-11', '0001', '4152', 356.25 UNION ALL 
    SELECT '2016-09', '0001', '5665', 866.25 UNION ALL 
    SELECT '2016-10', '0001', '5665', 656.25 UNION ALL 
    SELECT '2016-11', '0001', '5665', 356.25 

/*
Expected Output
'2016-09','0001','4152',1468.75
'2016-09','0001','5665',1878.75
*/

有人能帮我弄到这个吗？

Answer 1

这应该可以做到：

select min(MNTH), BR, CUSTNO, sum(SPAMT)
from @mytable
where MNTH between '2016-09' and '2016-11'
group by  BR, CUSTNO

或者说是具体的。

select min(MNTH), BR, CUSTNO, sum(SPAMT)
from @mytable
where MNTH in ('2016-09', '2016-10', '2016-11')
group by  BR, CUSTNO