how to count values with using find_in_set?

Question

In my db, uid is the autoincrement value indicates user ids and u_follow shows the user that follows other users with their uid seperated comma. i want to count that how many followers has each user. How can i do that ?

uid        u_follow
1          2,3
2          1,3
3          1,2
4          NULL
5          2,3,4

Are you able to do this in your script? If possible I would instead recommend normalizing the data. — Aaron Hathaway, May 23 '11 at 22:46
http://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization-why-is-it-important — YXD, May 23 '11 at 22:55

score 4 · Accepted Answer · edited May 23 '17 at 12:26

Store one value per column, otherwise you just can't do relational queries. See this excellent discussion for an introduction to database normalization.

users

uid
...

followers

uid u_follow
1   2
1   3
2   1
2   3
3   1
3   2
5   2
5   3
5   4

Then:

select u_follow, count(*) as num_followers from followers group by u_follow

If you want to include users with no followers do something like:

with a as (
  select u_follow, count(*) as num_followers
  from followers group by u_follow
)
select users.uid, coalesce(a.num_followers,0)
from users outer join a on users.uid = a.u_follow

how to count values with using find_in_set?

1 Answers1