CODEWITHSUNDEEP
mysqlsqlaggregate-functions
user987
user987

Reputation: 33

SQL aggregate get monthly and yearly

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

Upvotes: 0

Views: 1073

Answers (2)

MatBailie
MatBailie

Reputation: 86706

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

Upvotes: 2

Fabio
Fabio

Reputation: 313

The rows for the whole years contain the NULL value in the month column

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

You can find a demo here

EDIT: ordered the result set as for requirement

EDIT2: I still don't understand why the solution was not acceptable before, I tweaked it a bit to make it coincide with the new prototype of the result

Upvotes: 0

Related Questions