我想计算上一年的增长百分比。
表格结构:
CustomerName | Sum_1415 | Sum_1516
我尝试过这个查询:
select CustomerName, SUM(Sum_1415), SUM(Sum_1516),
round(sum(Sum_1516)-Sum(Sum_1415)/(select sum(Sum_1415) from dummy),2) as Percentage
from dummy
group by CustomerName
order by Sum_1415 desc
limit 15
仅用sum(Sum_1415)
替换子查询(select sum(Sum_1415) from dummy)
。否则,您计算的是每个客户的增长与上一年所有收入总额的比率,而不是该客户的总额。
在ROUND
调用中,在减法sum(Sum_1516)-Sum(Sum_1415)
周围也缺少一些括号。
select CustomerName, SUM(Sum_1415) AS Sum_1415, SUM(Sum_1516) AS Sum_1516,
round((sum(Sum_1516)-Sum(Sum_1415))/SUM(Sum_1415),2) as Percentage
from dummy
group by CustomerName
order by Sum_1415 desc
limit 15
我可能会将其重构为一个子查询,这样就不必不断重复SUM
表达式。
SELECT CustomerName, Sum_1415, Sum_1516, ROUND((Sum_1516-Sum_1415)/Sum_1415, 2) AS Percentage
FROM (SELECT CustomerName, SUM(Sum_1415) AS Sum_1415, SUM(Sum_1516) AS Sum_1516
FROM dummy
GROUP BY CustomerName
ORDER BY Sum_1516 DESC
LIMIT 15) AS x