Group by a string with delimiter

Question

I have a table where i save for every record his ancestors

+----+------------+
| id | ancestors  |
+----+------------+
|  1 | ,1,        |
|  2 | ,2,        |
|  3 | ,3,1,      |
|  4 | ,4,2,      |
|  5 | ,5,3,1,    |
|  6 | ,6,4,2,    |
|  7 | ,7,5,3,1,  |
+----+------------+

How to group by id but like this way group by ','id',' and not group by id to get a result like this :

+----+------------+
| id | count      |
+----+------------+
|  1 | 4          |
|  2 | 3          |
|  3 | 3          |
|  4 | 2          |
|  5 | 2          |
|  6 | 1          |
|  7 | 1          |
+----+------------+

My requirement is to find the count of the id in the whole column of ancestors.

Answer 1

SELECT  m1.id, COUNT(*)
FROM    mytable m1
JOIN    mytable m2
ON      FIND_IN_SET(m1.id, m2.ancestors)
GROUP BY
        m1.id

Note that it's not an efficient method and it will become slower as your database grows larger.

Answer 2

Your requirement seems to be to find the count of the id in the whole column of ancestors.

Hence, the using COUNT in a dependent subquery should be fine, as below:

SELECT a.id, 
(SELECT count(*) FROM ancestors_table t where t.ancestors LIKE CONCAT('%,',a.id,',%') )
  FROM ancestors_table a; 

Working SQLFiddle here.

UPDATE: Fixed the issue for two-digit or more figures. 1 will match only 1. Not 10, 11, etc. This is possible because your column appends , around each and every value.