Re: nested select within a DISCTINCT block

On 9/14/06 1:13 PM, "zqzuk" <ziqi(dot)zhang(at)hotmail(dot)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