SELECT sg.date, sg.groupName, sg.highlights, user.display_name as displayName,
        COUNT(*) as cmtcnt
        FROM `saved_groups` as sg
        LEFT JOIN `user` on user.email = sg.userName
        LEFT JOIN `user_comments` as uc on sg.groupName = uc.groupName
        GROUP BY sg.groupName

I have two tables, saved_groups and user_comments and the second line of the query should (does, it works) return an extra column with the number of comments associated with each group.

However, when a group has zero comments associated with it, the count returns 1 when it should return zero.

How can I fix this query?

I tried: COUNT(*)-1 as cmtcnt -- and that returns zero for the groups without comments, but it also returns an incorrect number (-1) for groups that have associated comments.

I also tried: NULL(Count(*), 0) -- but that errors out with #1582 - Incorrect parameter count in the call to native function 'ISNULL'

I also tried: COALESCE(COUNT(*), 0) as cmtcnt -- and that made no difference whatsoever (returned 1 for all groups with zero comments, correct number for the others)

Suggestions?

CodePudding user response：

COUNT() aggregate function never returns null and COUNT(*) always returns a value greater than 0 because it counts all the rows of the resultset.

What you want is to count the rows of user_comments that match the conditions in the ON clause and you can do this by specifying inside the parentheses the column groupName:

COUNT(uc.groupName)

This way only non-null rows will be counted and you can get 0 if for a specific sg.groupName there is no matching row in user_comments.

CodePudding user response：

You are counting all things from all tables when you say count(*). Because saved_groups has a row, you get the 1 returned, which is correct.

What you want is to only count the comments. So instead of COUNT(*) as cmtcnt you should use COUNT(uc.*) as cmtcnt.

This tells the query to only count rows from the uc table.