From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: simple join is beating me
Date: Mon, 13 Jul 2009 11:34:14 +0100
User-Agent: KMail/1.9.10
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200907131134.14335.gary.stainburn@ringways.co.uk>

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