User-Agent: Microsoft-Entourage/11.2.5.060620
Date: Thu, 14 Sep 2006 17:58:48 -0400
Subject: Re: nested select within a DISCTINCT block
From: Daryl Richter <daryl@eddl.us>
To: zqzuk <ziqi.zhang@hotmail.com>,
	<pgsql-sql@postgresql.org>
Message-ID: <C12F48D8.54FF%daryl@eddl.us>
Thread-Topic: [SQL] nested select within a DISCTINCT block
Thread-Index: AcbYSPSfMvQ6KEQ8Edu6HwARJOT9EA==
In-Reply-To: <6310575.post@talk.nabble.com>
Mime-version: 1.0
Content-type: text/plain;
	charset="ISO-8859-1"
Content-transfer-encoding: quoted-printable

On 9/14/06 1:13 PM, "zqzuk" <ziqi.zhang@hotmail.com> wrote:

>=20
> Hi, here i have a problem with this task...
>=20
> I have a table "cancellation" which stores cancelled bookings and details=
 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, an=
d
> all these are cancelled and therefore exist in cancellation, then the que=
ry
> 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 cancellation=
 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, unsurprising=
ly.
>=20
>=20
> i also tried=20
> select distinct b.product_package_id,
> count (distinct b.customer_id not in (select cc from cancellation cc wher=
e
> 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

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);=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' );

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 =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;

 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=B9m afraid of the easy stuff=8A its always harder than it seems=8A"
-- Bill Hampton, 2006