MySQL用一个条件对列求和

Question

我有两列，增益和偏差。我可以使用下面的代码得到一个和和我的计算。

sum(gain) - sum(bia) AS 'TOT YAC',

但是我还需要为bia列做另一个计算，其中另一个列满足条件(series_end='Touchdown')。但是我想在不改变下面的原始SELECT语句的情况下做到这一点。

SELECT possession_team AS Team, ball_carrier_receiver AS Player,
FROM plays WHERE run_pass='P' AND pass_result <>'S' AND pass_result <>'R'
GROUP BY possession_team, ball_carrier_receiver;

这就是我被卡住的地方。从逻辑上讲，它会像下面的东西，但我似乎不能得到任何工作在MySQL代码。不能是else 1 then 0 end&;因为我需要bia列的真和(值的范围可以从-5到50)。

sum(bia if series_end='Touchdown') AS 'BIA TD',

Answer 1

根据我的理解，您要求您需要使用CASE语句

SELECT possession_team Team, ball_carrier_receiver Player,
SUM(gain) - SUM(bia) 'TOT YAC',
SUM(bia CASE WHEN series_end = 'Touchdown' THEN 1 ELSE 0 END) 'BIA TD',
FROM plays 
WHERE run_pass = 'P' 
AND pass_result NOT IN ('S', 'R')
GROUP BY possession_team, ball_carrier_receiver;