From: zqzuk <ziqi.zhang@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: nested select within a DISCTINCT block
Date: Thu, 14 Sep 2006 15:11:12 -0700 (PDT)
Message-ID: <6315840.post@talk.nabble.com> (raw)
In-Reply-To: <C12F48D8.54FF%daryl@eddl.us>
References: <6310575.post@talk.nabble.com>
<C12F48D8.54FF%daryl@eddl.us>
Thanks alot!!!
Daryl Richter-2 wrote:
>
> On 9/14/06 1:13 PM, "zqzuk" <ziqi.zhang@hotmail.com> wrote:
>
>>
>> Hi, here i have a problem with this task...
>>
>> I have a table "cancellation" which stores cancelled bookings and details
>> of
>> charges etc
>> and a table "bookings" which stores details of bookings, for example:
>>
>> cancellation(cancellation_id, booking_id, charge)
>> booking(booking_id, customer_id, product_package_id, details)
>>
>> in the query, i wish to find, how many customers have booked for each
>> product_package_id. if there were 3 bookings for product_package_id=1,
>> and
>> all these are cancelled and therefore exist in cancellation, then the
>> query
>> result shoud display something like
>>
>> package_id, #of bookings
>> 1 0
>>
>>
>> here are what i tried
>>
>> select distinct b.product_package_id,
>> count (distinct b.customer_id and not exists (select cc from cancellation
>> cc
>> where cc.booking_id=b.booking_id)) from booking as b
>> group by b.product_package_id
>>
>> and it doesnt work. the syntax within the DISTINCT is wrong,
>> unsurprisingly.
>>
>>
>> i also tried
>> select distinct b.product_package_id,
>> count (distinct b.customer_id not in (select cc from cancellation cc
>> where
>> cc.booking_id=b.booking_id)) from booking as b
>> group by b.product_package_id
>>
>> it produced incorrect result. ie, for those canceled bookings are also
>> counted, producing
>> package_id, #of bookings
>> 1 3
>>
>> which supposed to be
>> package_id, #of bookings
>> 1 0
>>
>>
>> could anyone give any hints please, many thanks !
>>
>
> 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
>
> 2 record(s) selected [Fetch MetaData: 2/ms] [Fetch Data: 0/ms]
>
> [Executed: 9/14/06 5:56:07 PM EDT ] [Execution: 86/ms]
>
>
> --
> Daryl
> http://itsallsemantics.com
>
> "I¹m afraid of the easy stuff its always harder than it seems"
> -- Bill Hampton, 2006
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
--
View this message in context: http://www.nabble.com/nested-select-within-a-DISCTINCT-block-tf2272951.html#a6315840
Sent from the PostgreSQL - sql forum at Nabble.com.
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: ziqi.zhang@hotmail.com
Subject: Re: nested select within a DISCTINCT block
In-Reply-To: <6315840.post@talk.nabble.com>
* 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