SQl Query : how to aggregate on part of date

Question

I am puzzling how to do this in SQL (Jet flavored).. It is beyond my basic SQL skills.

I have a reporting requirement to produce a list of products bought (ie. product name), and the sum of the number of those items bought. Easy so far, but I also need the result aggregated by calendar month to produce something similar to table below :

         AUG  JUL JUN JUL 
widgets  23   54  67  67 .....
gadjets  12   34  33  19 .....

• Orders table has a field called order_date
• Order_Items table has fields product_name and items_purchased_count
• The two tables join on order_id

Can someone help ? Is this even possible in a single query ? I have tried a number of things but can't get close.

Thanks in advance. Bazza

Answer 1

I wonder if something like this would work (don't know much about Jet):

select
    oi.product_name,
    sum(iif(month(o.order_date) = 1, oi.items_purchased_count, 0)) as JAN,
    sum(iif(month(o.order_date) = 2, oi.items_purchased_count, 0)) as FEB,
    ...
from orders o
inner join order_items oi on o.order_id = oi.order_id
group by oi.product_name