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 :(

Massimiliano · Accepted Answer · 2010-09-05 12:32:47Z

4

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.

answered Sep 5, 2010 at 12:32

Massimiliano

17k11 gold badges74 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jack Over a year ago

Brilliant, thanks Yacoder - I'm now using COUNT (DISTINCT comments.id) which works great :)

Collectives™ on Stack Overflow

MySQL COUNT can't count

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related