WITH ROLLUP not working properly

Question

Here I'm showing the year of the order in the first column, the month in the second, Order_id in the third. I want to display totals by month and by year and grand totals.

This is the result I'm trying to get:

Month total, Year total and Grand total.

Here's my query:

SELECT coalesce(extract(year FROM order_date), 'Grand Total') as 'Year'
    , CASE  when extract(year FROM order_date) is null then ' '
        when extract(month FROM order_date) is null then 'Year total'
        else extract(month FROM order_date)
    END as `Month`
    , CASE when extract(month FROM order_date) is null Then ' '
        else coalesce(oh.order_id, 'Month Total')
    END as 'Order ID'
    , sum(quantity * order_price) AS 'AmntDue'
    , sum(quantity) AS 'NumBooksPurch'
FROM a_bkorders.order_headers oh
JOIN a_bkorders.order_details od on oh.order_id = od.order_id
GROUP BY extract(year FROM order_date), extract(month FROM order_date), oh.order_id with rollup
;

But this what I get:

The month total seems to work fine but not the yearly total or the grand total. I can't understand what I'm doing wrong.

Answer 1

I can't guarentee this will work because I don't have all of the data (aka the order amounts from the second table).. but I think something like this will work. let me know if its goofing up.

SELECT
    Year, 
    IF(Year = 'Grand Total', ' ', Month), 
    IF(Month = 'Year Total' OR Year = 'Grand Total', ' ', Ordered_ID),
    AmntDue, 
    NumberOfBooksPurch 
FROM(
    SELECT
        COALESCE(Year, 'Grand Total') AS Year,
        CASE 
            WHEN Year IS NULL THEN ' ' 
            ELSE COALESCE(MONTH, 'Year Total') 
        END AS Month,
        Ordered_ID,
        AmntDue, 
        NumberOfBooksPurch
    FROM (
        SELECT   
            YEAR(order_date) AS Year, 
            MONTH(order_date) AS Month,
            CASE 
                WHEN MONTH(order_date) IS NULL THEN ' '
                ELSE COALESCE(order_id, 'Month Total')
            END as Ordered_ID,
            SUM(quantity * order_price) AS AmntDue,
            COUNT(order_id) AS NumberOfBooksPurch
        FROM a_bkorders.order_headers
        JOIN a_bkorders.order_details USING (order_id)
        GROUP BY Year, Month, order_id WITH ROLLUP
    ) temp_table_alias
) final_temp_table;