From: Niklas Johansson <spot@tele2.se>
To: Daryl Richter <daryl@eddl.us>
Cc: zqzuk <ziqi.zhang@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: nested select within a DISCTINCT block
Date: Fri, 15 Sep 2006 03:24:29 +0200
Message-ID: <88EB0B61-90B2-49D4-AAD5-D5BA7164D50D@tele2.se> (raw)
In-Reply-To: <C12F48D8.54FF%daryl@eddl.us>
References: <C12F48D8.54FF%daryl@eddl.us>
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
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: spot@tele2.se, daryl@eddl.us, ziqi.zhang@hotmail.com
Subject: Re: nested select within a DISCTINCT block
In-Reply-To: <88EB0B61-90B2-49D4-AAD5-D5BA7164D50D@tele2.se>
* 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