查找比上一年增加的百分比

ve7v8dk2 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(319)

老实说，我不知道从这个剧本开始。我可以产生结果，显示我，每月，总收入的销售，从销售表。
以下脚本：

SELECT coalesce(date_format(TRANSACTION_DATE, '%M'), 'Grand Total') AS MONTH,
ROUND(SUM(SALES_AMOUNT),2) TOTAL
FROM SALES
WHERE YEAR(TRANSACTION_DATE) = 2018
GROUP BY month

结果：（哪些是好的，哪些是正确的）

Month   Total
January 45163.22
February    34531.15
March   44194.98
April   30826.68
May 39016.04
June    37284.25
July    37640.08
August  39703.85
September   34958.94
October 34447.61
November    39161.9
December    36986.16

但是，我想得出以下结果…即1月为空，2月为-10632.07，相当于减少了23.54%

Difference  Percentage Increase/Decrease
January 45163.22        
February    34531.15    -10632.07   -23.54
March   44194.98    9663.83 27.99
April   30826.68    -13368.3    -30.25
May 39016.04    8189.36 26.57
June    37284.25    -1731.79    -4.44
July    37640.08    355.83  0.95
August  39703.85    2063.77 5.48
September   34958.94    -4744.91    -11.95
October 34447.61    -511.33 -1.46
November    39161.9 4714.29 13.69
December    36986.16    -2175.74    -5.56

希望这有道理，如果有人能帮我，那就太好了。
谢谢您

mysql

来源：https://stackoverflow.com/questions/49957904/find-percentage-increase-from-previous-year

2条答案

按热度按时间

bq3bfh9z1#

通过在当月加入销售，并将第二次销售限制在前一年，您将拥有这两年的匹配月份。

SELECT 
    coalesce(date_format(a.`TRANSACTION_DATE`, '%M'), 'Grand Total') AS `MONTH`,
    ROUND(SUM(a.`SALES_AMOUNT`),2) AS `TOTAL_2018`,
    ROUND(SUM(b.`SALES_AMOUNT`),2) AS `TOTAL_2017`,
    ROUND(SUM(a.`SALES_AMOUNT`),2) - ROUND(SUM(b.`SALES_AMOUNT`),2) AS `Difference`,
    ROUND((ROUND(SUM(a.`SALES_AMOUNT`),2) - ROUND(SUM(b.`SALES_AMOUNT`),2)) / ROUND(SUM(b.`SALES_AMOUNT`),2),2) AS `Percentage_Difference`
FROM `SALES` a
JOIN `SALES` b
ON YEAR(b.`TRANSACTION_DATE`) = YEAR(a.`TRANSACTION_DATE`) - 1 AND 
    MONTH(a.`TRANSACTION_DATE`) = MONTH(b.`TRANSACTION_DATE`)
WHERE YEAR(a.`TRANSACTION_DATE`) = 2018
GROUP BY a.coalesce(date_format(a.`TRANSACTION_DATE`, '%M'), 'Grand Total')
ORDER BY a.coalesce(date_format(a.`TRANSACTION_DATE`, '%M'), 'Grand Total');

请注意，通过删除where子句并将年份添加到group by和order by子句中，您可以生成一个多年的报表。

赞(0）回复(0）举报 2021-06-21

jk9hmnmh2#

你可以加入

select a.MONTH, a.TOTAL, a.TOTAL - b.TOTAL,  ((a.TOTAL - b.TOTAL)/ a.TOTAL )*100
    from (

    SELECT coalesce(date_format(TRANSACTION_DATE, '%M'), 'Grand Total') AS MONTH,
    ROUND(SUM(SALES_AMOUNT),2) TOTAL
    FROM SALES
    WHERE YEAR(TRANSACTION_DATE) = 2018
    GROUP BY month
     ) a
    inner  join (
    SELECT coalesce(date_format(TRANSACTION_DATE, '%M'), 'Grand Total') AS MONTH,
    ROUND(SUM(SALES_AMOUNT),2) TOTAL
    FROM SALES
    WHERE YEAR(TRANSACTION_DATE) = 2017
    GROUP BY month
     ) b ON a.month = b.month

赞(0）回复(0）举报 2021-06-21

我来回答

查找比上一年增加的百分比

2条答案

相关问题

热门标签

最新问答