CODEWITHSUNDEEP
mysqlsql
Amadou Beye
Amadou Beye

Reputation: 2808

SQL How to count items by association

Table users

ID   name   country
---  -----  -----
100  John    1
101  Mark    4
102  Peter   3
103  Lucy    3

Table followers

ID   folowerId  followedId 
---  ---------  ------
 1   102         101
 2   103         101
 3   102         100
 4   100         101
 5   100         102
 5   103         100

Table country

ID   name 
---  -----
1    China
2    Usa
3    Italia
4    India

How to get the number of followers for a specific user grouped by country ?

Example output for user 101:

country   count
-------   -----
China      1 
Italia     2

I have tried this:

 SELECT 
     User.name, COUNT(Follower.id) AS FollowerCount 
 FROM 
    followers AS Follower 
    JOIN users AS User ON Follower.UserId = User.id 
 GROUP BY 
    User.country;

but it's not working

Upvotes: 1

Views: 460

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You should use a count and group by 

select c.name, count(*) 
from followers b 
left join users  as a on b.folloswerId = a.id
left join country on a.country = c.id
where b.followedId  = 101
group by c.name

Upvotes: 1

Related Questions