Re: nested select within a DISCTINCT block

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.