MySQL COUNT cannot count

Question

Well, maybe, but I can’t ask;)

Here is my request:

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 

An important line is the second - I'm indented. I ask COUNT to indicate the number of comments for a specific post, but it does not return the correct number. For example, something with two comments will return "1". Something with 8 comments from two different authors will still return "1" ...

Any ideas?

Thank!

Jack

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

Answer 1

code.id, (DISTINCT comments.codeid), comments.codeid = code.id, JOIN, 1.

- ... , COUNT(comments.commentid).

, , , , COUNT(*).