COUNT DISTINCT

I'm trying to use COUNT (DISTINCT ...) to count up the number of unique records in a table.

The table structure is:

PREFERENCEID
HOTELID
ROOMID
ROOMTYPE
BOOKDATE

and there could be lots of BOOKDATES.

What I want to do is count up how many roomtypes there are.

I've tried several things, with the following begin my best try

SELECT PREFERENCEID, HOTELID, ROOMTYPE, COUNT (DISTINCT ROOMTYPE) FROM HOTELONLINE GROUP BY
PREFERENCEID, HOTELID

However it gives an answer that includes all the records for each roomtype, instead of only the distinct ones:

Thus:

PREFERENCEID HOTELID ROOMTYPE COUNT
444 5 Single 3

Should actually have a count of 1

I'm doing something wrong - but I can't figure it out! Can anyone help me please?

Thanks

Hello,
..COUNT DISTINCT is illegal in all known SQL-dialects by me.

Better :
Make a query for every entity to count and fetch the amount of rows :

$res = mysql_query("SELECT roomtype FROM yourtable group by roomtype ");

$amountroomtypes = mysql_num_rows($res);

Other DBs are possibly analogous.

Regards
Ingo

Thanks - at least I know I can't do it

The question is - why does it appear in the mysql documentation as legal?

Here's the link:

What do you think?

Regards

Elizabeth