Page 1 of 1

Including all records in first count...

Posted: Fri Feb 16, 2007 10:41 am
by GeXus
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.

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_dt
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!

Posted: Fri Feb 16, 2007 10:53 am
by GeXus
Ok, I take that back. It looks like it is counting the first record in increments of 4.. The actual data was

user.name reviewcount
xxx 8
xxx 3

and If i had a new record for the first one, it turns to 12, then 16, etc. But if I had one to the second record, it goes to 4, 5, etc. Still not sure why it would do this.