Thread: nested select within a DISCTINCT 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-DISCTINCT-block-tf2272951.html#a6310575 Sent from the PostgreSQL - sql forum at Nabble.com.
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 12
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
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.
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