Including all records in first count...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Including all records in first count...

Post 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!
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post 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.
Post Reply