求一Sql语句

[ttg520]

id transAmt transDate payToOffer backToOrder
1 20.3200 2007-02-03 00:00:00.000 0 0
2 23.3200 2007-03-03 00:00:00.000 1 0
3 100.3200 2007-03-03 00:00:00.000 0 0
4 200.3200 2007-05-03 00:00:00.000 0 0
5 220.3200 2007-04-03 00:00:00.000 1 0
6 10.3200 2007-05-03 00:00:00.000 0 0
7 20.3200 2007-05-03 00:00:00.000 0 0
8 20.3200 2007-06-03 00:00:00.000 0 1
9 20.3200 2007-07-03 00:00:00.000 0 0
10 20.3200 2007-07-03 00:00:00.000 0 0
11 20.3200 2007-08-03 00:00:00.000 0 1
12 20.3200 2007-09-03 00:00:00.000 0 0
13 20.3200 2007-10-03 00:00:00.000 0 0
14 20.3200 2007-10-03 00:00:00.000 0 1
15 20.3200 2007-12-03 00:00:00.000 0 0
16 20.3200 2007-12-03 00:00:00.000 0 0
17 20.3200 2007-12-03 00:00:00.000 0 1
18 20.3200 2007-12-03 00:00:00.000 1 0
我想根据每季度来对transAmt求和,再根据payToOffer=1 or backToOrder=1表示金额已清分了clearAmt, unclearAmt根据 payToOffer=1 and backToOrder=1 最后达到如下效果
puarter clearAmt unclearAmt
1 23.3200 123.64
2 220.3200 251.28
3 20.32 60.96
4 60.96 40.64

[昵称被占用了]

结果好像贴错了点

select datepart(quarter,transDate) as puarter,
sum(case when payToOffer=1 or backToOrder=1 then transAmt else 0 end) as clearAmt,
sum(case when payToOffer=1 or backToOrder=1 then 0 else transAmt end) as unclearAmt
from tablename
group by datepart(quarter,transDate)

[chuifengde]

不知道你结果如何得到的
select
a=datepart(qq,transDate),
b=sum(case when paytooffer=1 or backtoorder =1 then transamt end),
c=sum(case when paytooffer=1 and backtoorder =1 then transamt end)
from [Table] group by datepart(qq,transDate)

[ttg520]

这里写错了 unclearAmt根据 payToOffer=0 and backToOrder=0

[ttg520]

搞定 谢谢