...

View Full Version : Rewrite this query legally



johnnyb
09-15-2006, 01:30 AM
I want to do this:


SELECT COM_idLST, COUNT(idCOMMENT) AS Comments
FROM COMMENT
GROUP BY idCOMMENT
WHERE Comments > 5


But as we know I can't use aliases in the WHERE clause. I tried putting the COUNT() function there as well but that didn't work. How can I write a query that will do what I want it to, (I think the desired result is apparent from the bad code above), but is actually possible?

Thanks in advance,
John

felgall
09-15-2006, 02:04 AM
Try this (not sure if it works in mySQL)

HAVING Comments > 5

johnnyb
09-15-2006, 02:43 AM
I was able to run the query successfully. I'll do some testing and see if it returns the results I expect.

johnnyb
09-15-2006, 03:40 AM
That was exactly right. Thank you very much.

guelphdad
09-15-2006, 02:36 PM
Note a WHERE clause must come before a GROUP BY clause and a HAVING clause comes after the GROUP BY clause. A HAVING clause should only be used when you are using a GROUP BY clause as it pertains to the GROUP BY and not individual rows.

johnnyb
09-15-2006, 06:47 PM
Yeah, I ditched the WHERE entirely and used HAVING to create a condition.

guelphdad
09-15-2006, 06:55 PM
Sure, I figured you had done that. Was just clarifying the point for those not familiar with using a HAVING clause.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum