MySQL Query COUNTing incorrectly

Question

Well, I realise that in fact it's more likely to be my logic that's counting wrong ;)

So here's my query:

SELECT 
  code.id AS codeid, code.title AS codetitle, code.summary AS codesummary, code.author AS codeauthor, code.date,
  code_tags.*, 
  tags.*, 
  users.firstname AS authorname, 
  users.id AS authorid,
  ratingItems.*, FORMAT((ratingItems.totalPoints / ratingItems.totalVotes), 1) AS rating,
  GROUP_CONCAT(tags.tag SEPARATOR ', ') AS taggroup,
  COUNT(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
GROUP BY code_id
ORDER BY date DESC
LIMIT 0, 15 

Sorry there's quite a bit of 'bloat' in that. The problem I'm having is with commentcount or (COUNT(comments.codeid) AS commentcount) - I want to count the total number of comments a code submission has. Previously, it was working correctly but I restructured my MySQL syntax and now it's stopped working :(

There's only 2 code submissions in the database that have comments. The first of these results that is returned correctly identifies it has more than 0 comments, but reports it as having '2' in commentcount, when in fact it has only 1.

The second submission ALSO only has one comment, but, it tells me that it has 4!

Can someone tell me what's wrong my logic here?

Thanks!

Jack

Answer 1

Try eliminating the GROUP BY constraint. Then see where your duplicate rows are coming from, and fix your original query. This will fix your COUNTs as well.

Answer 2

Try either:

COUNT(DISTINCT comments.codeid) AS commentcount

or

(SELECT COUNT(*) FROM comments WHERE comments.codeid = code.id) AS commentcount

Answer 3

Try starting with a simple query, and building up from that. If I have understood your structure correctly, the following query will return the correct number of comments for each code submission:

SELECT code.*, COUNT(code.id) AS comment_count
FROM code
JOIN comments ON comments.codeid = code.id
GROUP BY(code.id);

There's a few odd looking column names and joins in your example, which may be contributing to the problem … or it might just be an odd naming scheme :-)

For example, you are joining ratingItems to code by comparing code.id with ratingItems.uniqueName. That might be correct, but it doesn't quite look right. Perhaps it should be more like:

LEFT JOIN ratingItems ON ratingItems.code_id = code.id

Start with a basic working query, then add the other joins.