SQL聚合每月和每年获得

Question

我试图获得每月总计的汇总，并在相同的SQL查询中添加额外的年度总计行。

Table table_1

id    date        amount   currency

 1    2017-01-01  76.89     CAD
 2    2017-01-17  90.89     CAD
 3    2017-01-18  65        USD
 4    2017-05-13  45        CAD
 5    2017-07-19  76.70     CAD
 6    2018-08-13  67.34     CAD
 7    2018-09-11  50        CAD
 8    2018-09-09  45        CAD
 9    2018-08-12  67        CAD
 10   2018-07-10  55        USD
 11   2018-07-11  13        USD

我已经尝试过这个查询获得每月和按货币分组的总数。

    SELECT SUM(amount),
           currency, 
           MONTH(date)
   FROM invoices 
   GROUP BY MONTH(date), YEAR(date), currency

我想要的结果原型

结果原型

谢谢

Answer 1

整年的行包含月份列中的NULL值。

SELECT MONTH(date) AS Month,
       YEAR(date) AS Year,
       currency AS Currency,
       SUM(amount) AS Total
FROM invoices 
GROUP BY MONTH(date), YEAR(date), currency
UNION ALL
SELECT NULL, YEAR(date), currency, SUM(amount)
FROM invoices 
GROUP BY YEAR(date), currency
ORDER BY Year, Month IS NULL, Month

您可以找到一个演示这里

编辑:按需要排序结果集

EDIT2:我仍然不明白为什么以前这个解决方案是不可接受的，我对它做了一些调整，以使它与新的结果原型相一致。

Answer 2

SELECT列或ORDER列的顺序可能与GROUP列的顺序不同。

然后，您可以使用GROUP BY WITH ROLLUP并检查什么是使用GROUPING()的超级聚合，以选择要聚合到的级别，以及显示所有内容的顺序。

SELECT
   YEAR(date),
   MONTH(date),
   currency,
   SUM(amount)
FROM
   invoices
GROUP BY
   currency,
   YEAR(date),
   MONTH(date)
WITH
   ROLLUP
HAVING
   GROUPING(Currency) = 0  -- don't ROLLUP the currency
ORDER BY
   GROUPING(YEAR(date)),  -- individual years first, super-aggregate last
   YEAR(date),
   GROUPING(MONTH(date)), -- individual months first, super-aggregate last
   MONTH(date),
   currency