pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednested select within a DISTINCT block
2+ messages / 2 participants
[nested] [flat]
* nested select within a DISTINCT block
@ 2006-09-14 12:02 zqzuk <ziqi.zhang@hotmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: zqzuk @ 2006-09-14 12:02 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-DISTINCT-block-tf2271181.html#a6304397
Sent from the PostgreSQL - sql forum at Nabble.com.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: nested select within a DISTINCT block
@ 2006-09-22 17:28 Bruno Wolff III <bruno@wolff.to>
parent: zqzuk <ziqi.zhang@hotmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Bruno Wolff III @ 2006-09-22 17:28 UTC (permalink / raw)
To: zqzuk <ziqi.zhang@hotmail.com>; +Cc: pgsql-sql
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(*).
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-09-22 17:28 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-14 12:02 nested select within a DISTINCT block zqzuk <ziqi.zhang@hotmail.com>
2006-09-22 17:28 ` Bruno Wolff III <bruno@wolff.to>
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