From: Richard RK. Klingler <richard@klingler.net>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Sales report by month and item category
Date: Thu, 6 Aug 2015 16:16:33 +0000
Message-ID: <9FE72338-AD53-4A9B-B7FC-0129A0DF370D@klingler.net> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: richard@klingler.net
Subject: Re: Sales report by month and item category
In-Reply-To: <9FE72338-AD53-4A9B-B7FC-0129A0DF370D@klingler.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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