On 9/14/06 1:13 PM, "zqzuk" <[EMAIL PROTECTED]> 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