I need assistance with a query using group by with rollup

Question

I am trying to display the book order data showing the year of the order in the first column, the month in the second. Plus, display totals by month and by year and grand totals. Also, display messages "Yearly Total" and "Grand Total" instead of the nulls. The result is sorted by the year and month.

I keep receiving an error (Unknown column 'order_date' in 'field list') can anyone help me?

 select coalesce(year(order_date), 'Grand Total') as Year
, case when year(order_date) is null then ' ' else coalesce(month(order_date), 'Year Total') end as 
Month 
, AmntDue 
, NumberOfBooksPurch  
from (    
     select   year(order_date) as Year 
            , month(order_date) as Month
            ,  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(order_date), month(order_date), order_id with rollup
     ) tbl;

Answer 1

order_date is a value in the original table, but it's not being returned by the subquery so you can't reference it in the outer query. Use the aliases that the subquery returns:

select coalesce(Year, 'Grand Total') as Year
, case when Year is null then ' ' else coalesce(Month, 'Year Total') end as 
Month 
, AmntDue 
, NumberOfBooksPurch  
from (    
     select   year(order_date) as Year 
            , month(order_date) as Month
            ,  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
     ) tbl;