是否可以在一个选择查询中求和?
像这样的东西:
SELECT id, SUM(current_price - bought_price)*amount AS profit FROM purchase WHERE purchase_id = 1 GROUP BY id
我想要所有退货的总和 profit
这是您的查询:
SELECT id, SUM(current_price - bought_price)*amount AS profit
FROM purchase
WHERE purchase_id = 1
GROUP BY id ;
如果您希望获得总利润,只需删除group by
并改写sum()
:
SELECT SUM(current_price*amount - bought_price*amount) AS profit
FROM purchase
WHERE purchase_id = 1;
编辑:
您也可以这样写:
SELECT SUM((current_price - bought_price)*amount) AS profit
FROM purchase
WHERE purchase_id = 1;
顺便说一句,原来的表述是不正确的。您想将相同的形式用于group by
:
SELECT id, SUM((current_price - bought_price)*amount) AS profit
FROM purchase
WHERE purchase_id = 1
GROUP BY id;
您的版本存在的问题是,amount
每个版本都取自任意行id
。如果只有一个行purchase
的每个id
,那就没问题。但是,如果中有多个行id
,则问题中的查询将产生不确定的结果。(您正在使用此处group by
记录的MySQL扩展。)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句