MySQL & PHP: Joins & Counts

Question

I want to select all users from my table where access_level_id!=0. I also want to count the number of entries in another table where the user id matches a field (this could be 0 or 100).

I have:

SELECT users.id, count(clients.id) as count FROM $db[users] as users 
                              LEFT JOIN $db[clients] as clients ON users.id=clients.salesrep_id
                              WHERE users.access_level_id!='0'

This is returning just a single number (17)

This is because count is usually applied to all records. GROUP BY as suggested below should do the trick. — James P.
– James P., Commented Aug 24, 2011 at 22:28

Christoph Winkler · Accepted Answer · 2011-08-24 22:23:49Z

1

You have to group the results. Try adding GROUP BY users.id to your Query.

answered Aug 24, 2011 at 22:23

Christoph Winkler

6,4181 gold badge20 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL & PHP: Joins & Counts

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related