SQL Select help

[Anon]

I have a table like

Table ratings : int UserID, int CommentID, int Rating

and I know that the following query can get the average rating and the number of times a comment has been rated. How can I expand this statement to return a value to indicate whether or not the current user has already rated the comment, assuming I know the user's ID number?

SELECT count(*), AVG(rating) FROM ratings where commentID=#

Thank for any help,
K

[Anon]

SELECT count(*), AVG(rating) ,
sum(case when userid = # then 1 else 0 end) as rated
FROM ratings where commentID=#

This may not work in all dbms, so if you experience any problem, let us know which dbms you are using.

[Anon]

Thanks.

Now how would I get a list of commentIDs where the average rating is within a certain range?

My current approach is:

SELECT commentID
FROM ratings
WHERE (subquery) Between Min# and Max#

where subquery returns the avg rating of commentID:

SELECT avg(rating)
FROM ratings
WHERE CommentID=???

But how do I link the commentID from the main query to the subquery?

-K

[Anon]

select commentid,avg(rating)
from ratings
group by commentid
having avg(rating) between #low and #high