In-Reply-To: <C12F48D8.54FF%daryl@eddl.us>
References: <C12F48D8.54FF%daryl@eddl.us>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <88EB0B61-90B2-49D4-AAD5-D5BA7164D50D@tele2.se>
Cc: zqzuk <ziqi.zhang@hotmail.com>,
  <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: 7bit
From: Niklas Johansson <spot@tele2.se>
Subject: Re: nested select within a DISCTINCT block
Date: Fri, 15 Sep 2006 03:24:29 +0200
To: Daryl Richter <daryl@eddl.us>
Reply-To: spot@tele2.se


On 14 sep 2006, at 23.58, Daryl Richter wrote:
> create table booking(booking_id int, customer_id int,  
> product_package_id
> int, details text);
>
> create table cancellation(cancellation_id int , booking_id int, charge
> decimal);
>
> insert into booking values( 1, 1, 1, 'Cxl Booking 1' );
> insert into booking values( 2, 2, 1, 'Cxl Booking 2' );
> insert into booking values( 3, 2, 1, 'Ok Booking 3' );
> insert into booking values( 4, 3, 2, 'Cxl Booking 4' );
>
> insert into cancellation values( 1, 1, 1.00 );
> insert into cancellation values( 2, 2, 1.00 );
> insert into cancellation values( 3, 4, 1.00 );
>
>
> select distinct product_package_id,
>        ( select count(booking_id)
>          from booking b2
>          where
>             b2.product_package_id = b1.product_package_id
>         and not exists ( select 1 from cancellation c where  
> c.booking_id =
> b2.booking_id ) ) as uncancelled_bookings
> from booking b1
> order by product_package_id;
>
>  product_package_id     uncancelled_bookings
>  ---------------------  -----------------------
>  1                      1
>  2                      0

Given the above, you could also phrase it a little more natural, as  
follows:

SELECT product_package_id, COUNT(b.booking_id)-COUNT(c.booking_id) AS  
un_cancelled_bookings
FROM booking b
LEFT JOIN cancellation c USING(booking_id)
GROUP BY product_package_id
ORDER BY product_package_id;

I don't know about the amount and distribution of data in this case,  
but I think this will also give you a slightly better plan in most  
cases.


Sincerely,

Niklas Johansson