mysql - 根据货币计算 SUM

Question

我需要从以下查询中的所有货币值中获取总的 SUM，现在我得到 3 (或 4 - 取决于数据库中的货币)结果，但只需要总数。

请看这个 sqlFiddle

查询是:

select 
case when currency = 'GBP' then 
    sum(itemPrice) + sum(shippingPrice) * 1.33
else
    case when currency = 'USD' then
    sum(itemPrice) + sum(shippingPrice) * 0.92
else 
    case when currency = 'CAD' then 
    sum(itemPrice) + sum(shippingPrice) * 0.65
else 
    case when currency = 'MXN' then
    sum(itemPrice) + sum(shippingPrice) * 0.0513 
else sum(itemPrice) + sum(shippingPrice)
end end end end
as turnover, currency from foo
group by currency

当我尝试将所有情况包装在另一个 sum() 中时，我得到了

invalid use of group function

我怎样才能做到最好？

Answer 1

摆脱 GROUP BY，并执行 SUM outside CASE

SELECT SUM((itemPrice + shippingPrice) *
        CASE currency
            WHEN 'GBP' THEN 1.33
            WHEN 'USD' THEN 0.92
            WHEN 'CAD' THEN 0.65
            WHEN 'MXN' THEN 0.0513
            ELSE 1.0
        END) AS turnover
FROM foo