CODEWITHSUNDEEP
mysqlcount
max_
max_

Reputation: 24481

COUNT() returning incorrect number of rows

I am using the MySQL COUNT() method to find out how many rows in two tables have the uid of u.id.

To do so, I am using the method below:

SELECT u.id, u.first_name, u.last_name, u.email, u.username, COUNT( q.uid ) AS  `q_count`, COUNT( a.uid ) AS `a_count` 
FROM  `users` AS u
INNER JOIN `questions` AS q ON u.id = q.uid
INNER JOIN `answers` AS a ON u.id = a.uid
WHERE u.username =  'admin'

However, when I run the above query, the second count returns the same number of rows as the number of rows for the first count. I know this as the first count is returning two and the second is also doing the same, when there are two rows in the questions table and one row in the answers table.

Please can you tell me where I am going wrong?

Thanks

A sample of what I am receiving: http://d.pr/i/vcnJ

Sample data from answers: http://d.pr/i/TMkU

Sample data from questions: http://d.pr/i/tuwU

Upvotes: 4

Views: 1627

Answers (2)

Simon Forsberg
Simon Forsberg

Reputation: 13331

I believe the reason is that you're doing a JOIN, which will JOIN the results together into one. Do use the same query but with SELECT * instead and you will see why this happens.

Try this:

SELECT u.id, u.first_name, u.last_name, u.email, u.username, COUNT( DISTINCT q.id ) AS  `q_count`, COUNT( DISTINCT a.id ) AS `a_count` 
FROM  `users` AS u
INNER JOIN `questions` AS q ON u.id = q.uid
INNER JOIN `answers` AS a ON u.id = a.uid
WHERE u.username =  'admin'

Upvotes: 1

fancyPants
fancyPants

Reputation: 51878

You can't count two tables in one query. COUNT() counts rows, not specific values.

What you can do however is

SELECT u.id, u.first_name, u.last_name, u.email, u.username, 
SUM( CASE WHEN q.uid IS NULL THEN 0 ELSE 1 END ) AS  `q_count`, 
SUM( CASE WHEN a.uid IS NULL THEN 0 ELSE 1 END ) AS `a_count` 
FROM  `users` AS u
LEFT JOIN `questions` AS q ON u.id = q.uid
LEFT JOIN `answers` AS a ON u.id = a.uid
WHERE u.username =  'admin'

Upvotes: 0

Related Questions