count of all users who have added 100+ records

[bike5]

I need to get a count of all users who have added 100 or more codes into our system. Simplified table structure is:

table: users
cols: id

table: log_users
cols: user_id, action, extra

My query is:
SELECT count() as total FROM users u WHERE (SELECT count() FROM log_users l WHERE l.user_id = u.id AND l.action = 'enter_code' AND l.extra LIKE '%- Valid') >= 100

I think there is a better way to do this but I can't think of it right now. ANy suggestions?

Thank You

[djjjozsi]

You should use INNER JOIN to connect the actions table and the users table.
If you need to add a counted value into a condition, use HAVING instead.

You can count the userids in the log_users table with a query like this:

select count(*) from users WHERE id in (
   SELECT user_id 
   FROM `log_users`  

   WHERE action='enter_code'
   GROUP BY user_id
   HAVING (  COUNT(user_id)  > 1 )
)