pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSales report by month and item category
2+ messages / 2 participants
[nested] [flat]
* Sales report by month and item category
@ 2015-08-06 16:16 Richard RK. Klingler <richard@klingler.net>
2015-08-06 21:36 ` Re: Sales report by month and item category Steve Midgley <science@misuse.org>
0 siblings, 1 reply; 2+ messages in thread
From: Richard RK. Klingler @ 2015-08-06 16:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Sales report by month and item category
2015-08-06 16:16 Sales report by month and item category Richard RK. Klingler <richard@klingler.net>
@ 2015-08-06 21:36 ` Steve Midgley <science@misuse.org>
0 siblings, 0 replies; 2+ messages in thread
From: Steve Midgley @ 2015-08-06 21:36 UTC (permalink / raw)
To: Richard RK. Klingler <richard@klingler.net>; +Cc: pgsql-sql
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
>
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2015-08-06 21:36 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-08-06 16:16 Sales report by month and item category Richard RK. Klingler <richard@klingler.net>
2015-08-06 21:36 ` Steve Midgley <science@misuse.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox