SQL: How to get the total payment for each month and its monthly total

Question

I need to get the months of each corresponding data. For example, we will get the month from the date 2020-01-08(YYYY-MM-DD). It would seem that SELECT EXTRACT(MONTH FROM "2020-01-08") only gets from the date specified, but what I wanted was well, getting all of the months from the row at once.

I shall be using for the most part the ROLLUP subclause, but to those who have no idea what that is:

SELECT COALESCE(Brand, 'GrandTotal') AS Brand, SUM(Price) as 'TotalAmount'
FROM Purchase_Items
GROUP BY ROLLUP (Brand)

By the way, the COALESCE renames the NULL into GrandTotal and SUM adds all of the data in the Price row. So it will come out like this (with sample data that was given):

Brand              TotalAmount
-------------------------------
Apple              410000
Huawei             180000
Samsuing           261000
Xiaomi             110000
GrandTotal         961000

My next result has to look a lot more like this:

Month              Brand              TotalAmount
--------------------------------------------------
1                  Samsung            141000
1                  Monthly Total      141000
2                  Apple              170000
2                  Monthly Total      170000
3                  Huawei             135000
3                  Xiaomi             10000
3                  Monthly Total      145000
4                  Apple              70000
4                  Xiaomi             70000
4                  Monthly Total      140000
5                  Apple              170000
5                  Huawei             45000
5                  Samsung            120000
5                  Xiaomi             30000
5                  Monthly Total      365000
NULL               Grand Total        961000

What it did there was get all of the products' prices, adding them together and sorting accordingly to their months from their purchase dates. As simple as is sounds, I dunno exactly how to search this idea on Google or other FAQs, so I have no choice but to ask such a question here. I hope it is not as misleading or unclear as it seems.

Also, I am only advised to use ROLLUP only, so CUBE and PIVOT aren't options...

Answer 1

You seem to want:

SELECT MONTH(<datecol>),
       (CASE WHEN MONTH(<datecol>) IS NULL AND Brand IS NULL
             THEN COALESCE(Brand, 'Grand Total')
             WHEN Brand IS NULL THEN 'Month Total'
             ELSE Brand
        END) AS Brand,
       SUM(Price) as TotalAmount
FROM Purchase_Items
GROUP BY ROLLUP (MONTH(<datecol>), Brand)