Message-ID: <6315840.post@talk.nabble.com>
Date: Thu, 14 Sep 2006 15:11:12 -0700 (PDT)
From: zqzuk <ziqi.zhang@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: nested select within a DISCTINCT block
In-Reply-To: <C12F48D8.54FF%daryl@eddl.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
References: <6310575.post@talk.nabble.com> <C12F48D8.54FF%daryl@eddl.us>


Thanks alot!!!


Daryl Richter-2 wrote:
>=20
> On 9/14/06 1:13 PM, "zqzuk" <ziqi.zhang@hotmail.com> wrote:
>=20
>>=20
>> Hi, here i have a problem with this task...
>>=20
>> I have a table "cancellation" which stores cancelled bookings and detail=
s
>> of
>> charges etc=20
>> and a table "bookings" which stores details of bookings, for example:
>>=20
>> cancellation(cancellation_id, booking_id, charge)
>> booking(booking_id, customer_id, product_package_id, details)
>>=20
>> 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=3D1,
>> and
>> all these are cancelled and therefore exist in cancellation, then the
>> query
>> result shoud display something like
>>=20
>> package_id,   #of bookings
>> 1                  0
>>=20
>>=20
>> here are what i tried
>>=20
>> select distinct b.product_package_id,
>> count (distinct b.customer_id and not exists (select cc from cancellatio=
n
>> cc
>> where cc.booking_id=3Db.booking_id)) from booking as b
>> group by b.product_package_id
>>=20
>> and it doesnt work. the syntax within the DISTINCT is wrong,
>> unsurprisingly.
>>=20
>>=20
>> i also tried=20
>> select distinct b.product_package_id,
>> count (distinct b.customer_id not in (select cc from cancellation cc
>> where
>> cc.booking_id=3Db.booking_id)) from booking as b
>> group by b.product_package_id
>>=20
>> it produced incorrect result. ie, for those canceled bookings are also
>> counted, producing
>> package_id,   #of bookings
>> 1                  3
>>=20
>> which supposed to be
>> package_id,   #of bookings
>> 1                  0
>>=20
>>=20
>> could anyone give any hints please, many thanks !
>>=20
>=20
> create table booking(booking_id int, customer_id int, product_package_id
> int, details text);
>=20
> create table cancellation(cancellation_id int , booking_id int, charge
> decimal);=20
>=20
> 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' );
>=20
> insert into cancellation values( 1, 1, 1.00 );
> insert into cancellation values( 2, 2, 1.00 );
> insert into cancellation values( 3, 4, 1.00 );
>=20
>=20
> select distinct product_package_id,
>        ( select count(booking_id)
>          from booking b2
>          where
>             b2.product_package_id =3D b1.product_package_id
>         and not exists ( select 1 from cancellation c where c.booking_id =
=3D
> b2.booking_id ) ) as uncancelled_bookings
> from booking b1
> order by product_package_id;
>=20
>  product_package_id     uncancelled_bookings
>  ---------------------  -----------------------
>  1                      1
>  2                      0
>=20
>  2 record(s) selected [Fetch MetaData: 2/ms] [Fetch Data: 0/ms]
>=20
>  [Executed: 9/14/06 5:56:07 PM EDT ] [Execution: 86/ms]
>=20
>=20
> --
> Daryl
> http://itsallsemantics.com
>=20
> "I=C2=B9m afraid of the easy stuff=C2=8A its always harder than it seems=
=C2=8A"
> -- Bill Hampton, 2006
>=20
>=20
>=20
>=20
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>=20
>                http://www.postgresql.org/docs/faq
>=20
>=20

--=20
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.