SQL aggregate get monthly and yearly

Question

I am trying to get aggregate for monthly total and add extra row for yearly total into same SQL query.

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

I have tried with this query getting total monthly and group by currency.

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

Prototype of result which I want

prototype of result

Thank you

Answer 1

The order you SELECT columns or ORDER them can be different from the order you GROUP them.

Then you can use GROUP BY WITH ROLLUP and check what's a super-aggregate using GROUPING() for both choosing what levels to aggregate to, and what order to display everything.

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