Обсуждение: nested select within a DISTINCT block
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 ! -- View this message in context: http://www.nabble.com/nested-select-within-a-DISTINCT-block-tf2271181.html#a6304397 Sent from the PostgreSQL - sql forum at Nabble.com.
On Thu, Sep 14, 2006 at 05:02:25 -0700, 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 > > could anyone give any hints please, many thanks ! Left join booking and cancellation, select rows where cancellation_id IS NULL, GROUP BY either booking_id, and return that booking_id and count(*).