From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: simple join is beating me
Date: Mon, 13 Jul 2009 11:34:14 +0100
Message-ID: <200907131134.14335.gary.stainburn@ringways.co.uk> (raw)
hi folks
i have the following:
select o_ord_date as o_date, count(o_id) as orders
from orders where o_de_id in (5,6) and o_ord_date > CURRENT_DATE-'1
month'::interval
group by o_ord_date
order by o_date desc
and
select o_act_del_date as o_date, count(o_id) as delivery
from orders
where o_de_id in (5,6) and
o_act_del_date > CURRENT_DATE-'1 month'::interval and
o_act_del_date <= CURRENT_DATE
group by o_act_del_date
order by o_date desc
These give me
o_date | orders
------------+--------
2009-07-10 | 4
2009-07-09 | 5
2009-07-08 | 12
2009-07-07 | 5
2009-07-06 | 2
2009-07-03 | 2
2009-07-02 | 7
2009-07-01 | 19
2009-06-30 | 20
2009-06-29 | 28
and
o_date | delivery
------------+----------
2009-07-13 | 5
2009-07-10 | 3
2009-07-09 | 4
2009-07-08 | 2
2009-07-07 | 4
2009-07-06 | 7
2009-07-03 | 6
2009-07-02 | 5
2009-07-01 | 3
2009-06-30 | 3
How do i get
o_date | orders | delivery
------------+--------+----------
2009-07-13 | | 5
2009-07-10 | 4 | 3
2009-07-09 | 5 | 4
2009-07-08 | 12 | 2
2009-07-07 | 5 | 4
2009-07-06 | 2 | 7
2009-07-03 | 2 | 6
2009-07-02 | 7 | 5
2009-07-01 | 19 | 3
2009-06-30 | 20 | 3
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
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: gary.stainburn@ringways.co.uk
Subject: Re: simple join is beating me
In-Reply-To: <200907131134.14335.gary.stainburn@ringways.co.uk>
* 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