Re: Sales report by month and item category

Hello

I'm currently using following query to report the monthly revenue of an online shop:

(well…I just use pgsql on a KISS basis ;o)

select date_trunc('month', orders.orderdate) as month, sum(orderitems.price * orderitems.orderitems2quantity) as revenue

from orders, orderitems, product

where orderitems.orderitems2orderid = orders.orderid

and orderitems.orderitems2productid = product.productid

group by date_trunc('month', orders.orderdate)

order by month desc;

gives:

         month          | revenue  

------------------------+----------

 2015-08-01 00:00:00+02 |  1956.00

 2015-07-01 00:00:00+02 | 13079.40

 2015-06-01 00:00:00+02 | 10864.20

But as the "product" items have categories assigned to them I would like to be able to also

report based on categories, something like:

         month          |  bikes   |   cars   |  planes  | submarines 

------------------------+----------+----------+----------+------------

 2015-08-01 00:00:00+02 |  233.00  | 4211.00  | 7833.50  |  723.35

So far I'm using two queries called in a loop in my PHP code….but the problem there is that

not all categories return a revenue for a specific month if  nothing was sold in that month.

Or is that not possible in a more or less simple query?

thanks in advance

richard