I am trying to get aggregate for monthly total and add extra row for yearly total after each year into same SQL query.
Here is sample data
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
And this is how I want
I have tried with this two query but couldn’t not get exact result.
SELECT SUM(amount), currency, MONTH(date) AS MONTH, YEAR(date) AS YEAR FROM table_1 GROUP BY MONTH(date), YEAR(date), currency UNION ALL SELECT SUM(amount), currency, NULL, YEAR(date) FROM invoices GROUP BY YEAR(date), currency
SELECT currency, SUM(amount) AS sum12, MONTH(date),YEAR(date) FROM table_1 GROUP BY ROLLUP(MONTH(date), YEAR(date), currency)