group mysql count values in comma separated field

Question

I have a users table with columns: user_id, mechanic_id

and

mechanics table with id

I would like to count how many users have the same mechanic.

Users table

+-------------------------+
| user_Id   mechanic_id   |
+-------------------------+
| 1           1,2         |
| 2           2,1         |
| 3           2,1,8,16    |
| 4           1,16,3      |
+-------------------------+

mechanics table

+------+
| id   |
+------+
| 1    |
| 2    |
| 3    |
  ...

Count for $id1 is: 4

Count for $id2 is: 3

Count for $id3 is: 1

Count for $id8 is: 1

Count for $id16 is: 2

Why are you storing data as a comma-separated list? You should seriously consider normalizing your table. — Taryn, Jul 23 '13 at 19:26
Very similar to http://stackoverflow.com/questions/4155873/find-in-set-vs-in — DevlshOne, Jul 23 '13 at 19:26
@SašoKrajnc You're shooting yourself in the foot. You're violating the basic principles of database normalization. — Kermit, Jul 23 '13 at 19:31

score 1 · Answer 1 · answered Jul 23 '13 at 19:28

1

Best solution: scrap this table design and rebuild with a properly normalized once. Then a simple join + group by + count query will work.

Worst solution: use MySQL's find_in_set() function:

SELECT mechanics.id, COUNT(user_ID)
FROM mechanics
LEFT JOIN users ON (FIND_IN_SET(mechanics.id, users.mechanic_id) > 0)
GROUP BY mechanics.id

answered Jul 23 '13 at 19:28

Marc B

356,200
43
426
500

I combined your solution with mine and it is working...thank you! – Sašo Krajnc Jul 23 '13 at 20:12

score 1 · Accepted Answer · answered Jul 23 '13 at 20:11

I don't know why I am violating the basic principles of database normalization...Each user has usually one mechanic or max 2 or 3, so that's why I decided to store data in users table.

I found solution based on @Marc B:

SELECT count(*) FROM users a 
INNER JOIN mechanics b 
ON (FIND_IN_SET(b.id, a.mechanic_id) > 0)
WHERE b.id = '{$id}' 
group by b.id

score 0 · Answer 3 · answered Jul 23 '13 at 19:31

0

SELECT  COUNT(user_Id)
FROM    users, mechanics
WHERE   mechanics.id IN (users.mechanic_id)

answered Jul 23 '13 at 19:31

DevlshOne

8,357
1
29
37

group mysql count values in comma separated field

3 Answers3