CODEWITHSUNDEEP
mysqlleft-joinaggregate-functions
Ross
Ross

Reputation: 46987

Why is this aggregate query returning one less row than it should?

I have the classic 'get all rows in one table with number of corresponding rows in another table' issue which should be solved by this query:

SELECT
    ideas.id,
    ideas.idea,
    submitted,
    COUNT(votes.id) AS vote_count
FROM ideas
LEFT OUTER JOIN votes ON ideas.id = votes.idea
WHERE dead = 0
GROUP BY votes.idea
ORDER BY vote_count DESC, submitted DESC
LIMIT 10;

There are 4 rows (with dead = 0) in ideas and one row in votes (relating to the first idea). However this query returns two records (idea #1 and idea #2) with correct vote_counts. Why is this not returning all of the records in ideas?

Upvotes: 1

Views: 674

Answers (2)

Mark Byers
Mark Byers

Reputation: 838166

Change:

GROUP BY votes.idea

to:

GROUP BY ideas.id

Because votes.idea can be NULL.

Upvotes: 1

Larry Lustig
Larry Lustig

Reputation: 50970

When you say GROUP BY votes.idea, you are asking for one result row per idea value in votes. Since you say votes has only one row, you should expect only two records in the result — one corresponding to the idea value in that row of votes, and the other with NULL (condensing the three rows with no matching vote record).

Did you mean GROUP BY ideas.idea?

Upvotes: 3

Related Questions