[SOLVED] Conditions based upon COUNT(*) field

[mb81]

Here is my query:

SELECT COUNT(*) AS countreservations,reserveddate,resourceid FROM RESERVATIONS_reservedresources GROUP BY reserveddate,resourceid ORDER BY countreservations DESC

 

I want to get a set of results back where only where countreservations>1 is returned.

 

If I try to insert WHERE countreservations>1, it says it doesn't know that column

If I try to insert WHERE COUNT(*)>1, it says illegal use of group function

 

Is there any way to get just the records that have 2 for the COUNT(*)?

 

Thanks.

 

 

[gluck]

Use having clause

[gluck]

SELECT COUNT(*) AS countreservations,reserveddate,resourceid FROM RESERVATIONS_reservedresources GROUP BY reserveddate,resourceid  having count(countreservations) > 1

[mwasif]

SELECT COUNT(*) AS countreservations,reserveddate,resourceid FROM RESERVATIONS_reservedresources GROUP BY reserveddate,resourceid 
HAVING countreservations>1
ORDER BY countreservations DESC

[gluck]

mwasif is correct .. I meant to type count(*)

[mb81]

Cool, never had to use that before, thanks for the info, I knew there was something I was missing.

 

Thanks!

 

SOLVED

[mwasif]

You are welcome.