Including all records in first count...
Posted: Fri Feb 16, 2007 10:41 am
Has anyone experienced when doing joins and using count() that the first record will be a total count of all records, as opposed to the appropriate record, then the rest will be line.. Let me try and explain...
Here my query.
The issue is that on the 'reviewcount' it is displaying the following
user.name reviewcount
xxx 6
xxx 2
For the first one, the actual count is really only 4 and the second one is correct. So it is as though it is counting the total, applying that to the first record, then it counts appropriatly for the rest.
I have another query that is almost the same, giving me the same problem.. Thanks for your help!
Here my query.
Code: Select all
SELECT users.users_id AS users_id, count(reviews.active) as reviewcount, users.name AS name, users.created_dt AS date, count( content.content_id ) AS contentcount, replace( DATEDIFF( max(content.created_dt), now( ) ) , '-', '' ) AS datediff
FROM users
LEFT JOIN content ON users.user_id = content.user_id
LEFT JOIN reviews ON users.user_id = reviews.user_id
WHERE users.users_id = 27
AND users.active = 1
GROUP BY users.name
ORDER BY users.name, users.created_dtuser.name reviewcount
xxx 6
xxx 2
For the first one, the actual count is really only 4 and the second one is correct. So it is as though it is counting the total, applying that to the first record, then it counts appropriatly for the rest.
I have another query that is almost the same, giving me the same problem.. Thanks for your help!