pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednested select within a DISCTINCT block
4+ messages / 3 participants
[nested] [flat]
* nested select within a DISCTINCT block
@ 2006-09-14 17:13 zqzuk <ziqi.zhang@hotmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: zqzuk @ 2006-09-14 17:13 UTC (permalink / raw)
To: pgsql-sql
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-DISCTINCT-block-tf2272951.html#a6310575
Sent from the PostgreSQL - sql forum at Nabble.com.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: nested select within a DISCTINCT block
@ 2006-09-14 21:58 Daryl Richter <daryl@eddl.us>
parent: zqzuk <ziqi.zhang@hotmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Daryl Richter @ 2006-09-14 21:58 UTC (permalink / raw)
To: zqzuk <ziqi.zhang@hotmail.com>; pgsql-sql
On 9/14/06 1:13 PM, "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
>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: nested select within a DISCTINCT block
@ 2006-09-14 22:11 zqzuk <ziqi.zhang@hotmail.com>
parent: Daryl Richter <daryl@eddl.us>
1 sibling, 0 replies; 4+ messages in thread
From: zqzuk @ 2006-09-14 22:11 UTC (permalink / raw)
To: pgsql-sql
Thanks alot!!!
Daryl Richter-2 wrote:
>
> On 9/14/06 1:13 PM, "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
>>
>> 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
>
>
--
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: nested select within a DISCTINCT block
@ 2006-09-15 01:24 Niklas Johansson <spot@tele2.se>
parent: Daryl Richter <daryl@eddl.us>
1 sibling, 0 replies; 4+ messages in thread
From: Niklas Johansson @ 2006-09-15 01:24 UTC (permalink / raw)
To: Daryl Richter <daryl@eddl.us>; +Cc: zqzuk <ziqi.zhang@hotmail.com>; pgsql-sql
On 14 sep 2006, at 23.58, Daryl Richter wrote:
> 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
Given the above, you could also phrase it a little more natural, as
follows:
SELECT product_package_id, COUNT(b.booking_id)-COUNT(c.booking_id) AS
un_cancelled_bookings
FROM booking b
LEFT JOIN cancellation c USING(booking_id)
GROUP BY product_package_id
ORDER BY product_package_id;
I don't know about the amount and distribution of data in this case,
but I think this will also give you a slightly better plan in most
cases.
Sincerely,
Niklas Johansson
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-09-15 01:24 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-14 17:13 nested select within a DISCTINCT block zqzuk <ziqi.zhang@hotmail.com>
2006-09-14 21:58 ` Daryl Richter <daryl@eddl.us>
2006-09-14 22:11 ` zqzuk <ziqi.zhang@hotmail.com>
2006-09-15 01:24 ` Niklas Johansson <spot@tele2.se>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox