MySQL COUNT can't count

Question

Well, it can, but I can't query ;)

Here's my query:

SELECT code.id AS codeid, code.title AS codetitle, code.summary AS codesummary, code.author AS codeauthor, code.date, code.challengeid, ratingItems.*, FORMAT((ratingItems.totalPoints / ratingItems.totalVotes), 1) AS rating, code_tags.*, tags.*, users.firstname AS authorname, users.id AS authorid, GROUP_CONCAT(tags.tag SEPARATOR ', ') AS taggroup,
    COUNT(DISTINCT comments.codeid) AS commentcount
FROM (code)
JOIN code_tags ON code_tags.code_id = code.id
JOIN tags ON tags.id = code_tags.tag_id
JOIN users ON users.id = code.author
LEFT JOIN comments ON comments.codeid = code.id
LEFT JOIN ratingItems ON uniqueName = code.id
WHERE `code`.`approved` = 1
GROUP BY code_id
ORDER BY date desc
LIMIT 15 

The important line is the second one - the one I've indented. I'm asking it to COUNT the number of comments on a particular post, but it doesn't return the right number. For example, something with two comments will return "1". Something with 8 comments by two different authors will still return "1"...

Any ideas?

Thanks!

Jack

EDIT: Forgot to mention. When I remove the DISTINCT part, something with 8 comments from two authors returns "28". Sorry, I'm not a MySQL expert and don't really understand why it's returning that :(

Answer 1

You group by code.id and in each group you count (DISTINCT comments.codeid), but comments.codeid = code.id as defined in JOIN, that's why you always get 1.

You need to count by some other field on comments... if there is a primary surrogate key, this is the way to go COUNT(comments.commentid).

Also, if the comments in every group are known to be distinct, a simple COUNT(*) should work.