CODEWITHSUNDEEP
mysqljoincount
Milkbones
Milkbones

Reputation: 41

MySQL Count from one table, join to another table

Here's what I'm trying to do: Table A has a record of user logins Table B has the user's information

I want to computer the total number of logins for each user in table A, then join that to table B so my outcome is something like....

User 1 Name: John Logged In: 15 times User 2 Name: Mary Logged In: 22 times

Any help is greatly appreciated

Upvotes: 0

Views: 80

Answers (2)

Besnik
Besnik

Reputation: 6529

You need something like that:

SELECT u.*, count(l.id) AS login_count
FROM user s
LEFT JOIN login l ON u.id = l.user_id
GROUP login.id

Upvotes: 2

Vatev
Vatev

Reputation: 7590

SELECT
    users.*,
    COUNT(user_logins.user_id) as login_count
FROM users 
LEFT JOIN user_logins ON user_logins.user_id = users.user_id
GROUP BY users.user_id

If you don't need the users with 0 logins remove LEFT from the LEFT JOIN

Upvotes: 1

Related Questions