0

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;
user3051454
  • 1
  • 3

1 Answers1

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;
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Do you have any idea how I would, continuing with the same calculations, display the total lines only for the year totals? So that each Year is grouped each with an individual Yearly total. – user3051454 Dec 13 '13 at 06:35
  • I'm not sure what you mean. Update your question and show the desired output. Also, it would help if you made a sqlfiddle with sample data to play with. – Barmar Dec 13 '13 at 06:37
  • Not sure how to edit query in comment section without rearranging the format. – user3051454 Dec 13 '13 at 06:53
  • Don't put it in the comment section, use the `edit` link below your question to add to it. – Barmar Dec 13 '13 at 06:58