i get an error when i use COUNT(DISTINCT colum_name)

hello,

i have a working stored procedure

SELECT CommentID, UserName, PictureID, DateCommented, COUNT(CommentID) OVER (PARTITION BY PictureID) AS'NrOfComments'FROM Comments WHERE PictureID = @.PictureID

witch returns among others the number of comments for a picture

i need to select the number of distinct users who commented that user too, so i added this at SELECT statement

, COUNT(DISTINCT UserName) AS'Expr1'

i get that error:

"Colum 'Comments.CommentID' is invalig in the select list beacuse it is not contained in either an aggregate function or the GROUP BY clause."

what should i do, to select the number of distinct users who commented on a specific picture?

here are the table rows, if you need that

CommentID -- UserName -- PictureID -- DateCommented

please help me, thank you

You cannot get both the distinct users and the total number of comments for a picture in the same query. To use the COUNT function, you need to use GROUP BY.

For instance, to get the total number of comments for a given PictureID you would do:

SELECT PictureID,COUNT(*)FROM CommentsWHERE PictureID = @.PictureIDGROUP BY PictureID

If you would like to get the number of comments each user has made for a given PictureID you would do:

SELECT PictureID, UserName,COUNT(*)FROM CommentsWHERE PictureID = @.PictureIDGROUP BY PictureID, UserName

You will have to do two queries to get the data you want - one for the summary, and one for the actual rows.|||

ok, i understand...

i have one last question

Is possible to have two SELECT statements in the same stored procedure?

thank you for replays

|||

Basically, you can only return the result from one SELECT statement but it is possible to build this query as a UNION or JOIN with values that you need. However, this can be quite complex, and I would recommend you to split it into two procedures in this case.

|||

yes, two select statements are not possbile, but select in select works very good for what i was needed

SELECT CommentID, UserName, PictureID,(SelectCOUNT(CommentID)FROM UserPictures)AS 'Comments'
FROM UserPictures

thanks for replyes, the post is solved

|||

Ouch. I don't mean to scare you, but this will in fact count the total number of comments for every row in your table. Doing the same calculation over and over again, it will give you the same result for every row. Is this really what you want? It is ok as long as you are aware that there may be performance issues with this. It might be an easy solution, (and working) if you dont have zillions of rows in your table ;-)