COUNT DISTINCT

[Dagon]

This has bugged me for quite a long time. Does anyone know if you can put two columns into a "count distinct" ? For example, DB2 is quite happy with:

select service_id,
count(*) as no_of_guests
count(distinct room_id) as no_of_rooms
from bookings
group by service_Id

But if I try to change this to:

select service_id,
count(*) as no_of_guests
count(distinct booking_id, room_id) as no_of_rooms
from bookings
group by service_Id

I get a syntax error. Is there any way this can be done ? Obviously, I could pad out the booking_id and room_id to fixed numbers of spaces and concatenate them together to make a unique key, but I would have thought it should be possible without such measures.

 

[BrianTyler]

The nearest thing I can think of is to use OLAP functions - (grouping sets etc) - which are described well in the Cookbook.

 

[blom0344]

It might be an idea to post this to Graeme Birchall himself .
I wonder why you are performing a count (*) anyway? Any object will do with count() in this case?

Ties Blom
Information analyst

 

[MarcLodge]

Dagon,
I think that he constricts of DB2 and SQL will stop you from doing what you wish. OLAP functions are a difinite possibility, but I have the feeling that you may well spend a lot of time on them, and still not come up with the answer you require.

My suggestion would be to bite the bullet, and code the two fields into a concatenation key.

Marc

 

[Dagon]

I managed to get round it eventually by using another field which I could substring to get the equivalent of booking_id||room_id. This was a bit easier than trying to left pad numeric IDs with zeroes or spaces. I thought about using OLAP functions but I'd probably end up with lots of subselects, which would be a bit messy.