以下这段代码错在哪里了?

[coffeemilk]

以下这段代码错在哪里了?
我是统计某天分别action =1或者２的时候费用的总值是多少！

SELECT b.FeeCode AS FeeCode, b.Name AS name, b.SPServiceID AS SPServiceID,
(CASE WHEN action = 1 THEN SUM(CONVERT(int, b.FeeCode)) ELSE 0) adds,
(CASE WHEN action = 2 THEN SUM(CONVERT(int, b.FeeCode)) ELSE 0) adds1
FROM DZ a, Sms b
WHERE a.SPServiceID = b.SPServiceID AND (a.DzTime > '2006-05-10 00:00:00')
GROUP BY b.FeeCode, b.Name, b.SPServiceID

在线等

[子陌红尘]

SELECT
b.FeeCode AS FeeCode,
b.Name AS name,
b.SPServiceID AS SPServiceID,
SUM(CASE WHEN action=1 THEN CONVERT(int,b.FeeCode) ELSE 0 end) as adds,
SUM(CASE WHEN action=2 THEN CONVERT(int,b.FeeCode) ELSE 0 end) as adds1
FROM
DZ a, Sms b
WHERE
a.SPServiceID = b.SPServiceID AND (a.DzTime > '2006-05-10 00:00:00')
GROUP BY
b.FeeCode, b.Name, b.SPServiceID

[子陌红尘]

1、SUM()应该在CASE ... WHEN ... 之外，对表达式汇总

2、CASE ... WHEN ... END，一定要用END。

[zzit0721]

SELECT b.FeeCode AS FeeCode, b.Name AS name, b.SPServiceID AS SPServiceID,
(CASE WHEN action = 1 THEN SUM(CONVERT(int, b.FeeCode)) ELSE 0 end) adds,
(CASE WHEN action = 2 THEN SUM(CONVERT(int, b.FeeCode)) ELSE 0 end) adds1
FROM DZ a, Sms b
WHERE a.SPServiceID = b.SPServiceID AND (a.DzTime > '2006-05-10 00:00:00')
GROUP BY b.FeeCode, b.Name, b.SPServiceID

CASE语句少END结束,加上就好了

[hyrongg]

SELECT b.FeeCode AS FeeCode, b.Name AS name, b.SPServiceID AS SPServiceID,
(CASE WHEN action = 1 THEN SUM(CONVERT(int, b.FeeCode)) ELSE 0 end) adds,
(CASE WHEN action = 2 THEN SUM(CONVERT(int, b.FeeCode)) ELSE 0 end) adds1
FROM DZ a, Sms b
WHERE a.SPServiceID = b.SPServiceID AND (a.DzTime > '2006-05-10 00:00:00')
GROUP BY b.FeeCode, b.Name, b.SPServiceID

[itblog]

SELECT b.FeeCode AS FeeCode, b.Name AS name, b.SPServiceID AS SPServiceID,
(CASE WHEN action = 1 THEN SUM(CONVERT(int, b.FeeCode)) ELSE 0 end) adds,
(CASE WHEN action = 2 THEN SUM(CONVERT(int, b.FeeCode)) ELSE 0 end) adds1
FROM DZ a, Sms b
WHERE a.SPServiceID = b.SPServiceID AND (a.DzTime > '2006-05-10 00:00:00')
GROUP BY b.FeeCode, b.Name, b.SPServiceID

[coffeemilk]

各位，现在其实很简单．就是把action=1（订购） and action=2(取消) 的统计数列出来．
现在我用上面的方法．出现了得把action这字段得包括在GROUP 里．这样一来．统计出来的结果肯定多了

[coffeemilk]

不愧是五个红星的，厉害！就这样对了

[itblog]

这样可以满足你的要求

SELECT b.FeeCode AS FeeCode, b.Name AS name, b.SPServiceID AS SPServiceID,
SUM(CASE WHEN action = 1 THEN CONVERT(int, b.FeeCode) ELSE 0 end ) adds,
SUM(CASE WHEN action = 2 THEN CONVERT(int, b.FeeCode) ELSE 0 end) adds1
FROM DZ a, Sms b
WHERE a.SPServiceID = b.SPServiceID AND (a.DzTime > '2006-05-10 00:00:00')
GROUP BY b.FeeCode, b.Name, b.SPServiceID