Обсуждение: Sales report by month and item category

Поиск
Список
Период
Сортировка

Sales report by month and item category

От
"Richard RK. Klingler"
Дата:
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


Re: Sales report by month and item category

От
Steve Midgley
Дата:
If I'm reading your question right, you want to use a group by statement based on the category field of the table (or join table). You won't get a flat readout like you show, but you would get a row based output that way:

Date | Category | Count
xyz  | Bikes    |  233
xyz  | Cars     |  324

If you don't have a category field, add one to the table somehow for every product - that should make it work.

If you want Null (no data) fields to show up in your report, there are a few ways to do that. Off the top of my head maybe using "coalesce" to convert null to "0" would work (be careful as this can change averages etc). Sometimes a left/right or outer join will work and won't have the problem with summary data like coalesce.

Steve




On Thu, Aug 6, 2015 at 9:16 AM, Richard RK. Klingler <richard@klingler.net> wrote:
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