How to count each item in array with sql

Question

I have a user table which contain a membergroupids, and user table looks like this:

userid   membergroupids
1        1,2
2        2,3
3        2,3,4

and I want to use sql to output a result like this

membergroupid    count
1                1
2                3
3                2
4                1

I tried use SELECT membergroupids FROM user, then use php to loop through the result and get the count, but it works with small set of user table, but I have a really big user table, the select query itself will take more than 1min to finish, is there better way to do this?

Dont store CSVs in columns. https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad. — chris85, Apr 25 '18 at 00:38

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

There is a much better way to do it. Your tables need to be normalized:

Instead of

userid   membergroupids
1        1,2
2        2,3
3        2,3,4

It needs to be

userid   membergroupids
1        1
1        2
2        2
2        3
3        2
3        3
3        4

From here, it's a simple query to get the counts (assuming this table is called your_table:

select count(membergroupids) as numberofgroups, userid 
from your_table 
group by userid 
order by userid

The real problem, then, is getting your tables normalized. If you only have 9 membergroupids, then you could use a like '%1%' to find all userids with membergroupid #1. But if you have 10, then it won't be able to distinguish between 1 and 10. And sadly, you can't count on the commas to help you distinguish because the number might not be surrounded by commas.

unless...

Create new field with group ids encapsulated by commas

you could create a new field and populate it with membergroupids and surround it with commas by using concat (check your database's docs). Something along this line:

update your_table set temp=concat(',', membergroupids, ',');

This could give you a table structure like so:

userid   membergroupids  temp
1        1,2             ,1,2,
2        2,3             ,2,3,
3        2,3,4           ,2,3,4,

Now, you have the ability to grab distinct member group ids in the new field, ie, where temp like '%,1,%' to find userids with membergroupid 1. (They will be encapsulated by commas) Now, you can manually build your new normalized table which I'll call user_member.

Insert membergroupid 1:

insert into user_member (userid,membergroupid) select userid,'1' from your_table where temp like '%,1,%';

You could make a php script that loops through all the membergroupids.

Keep in mind that like %...% is not very efficient, so don't even think about relying on this to do your count. It'll work, but it's not scalable. It would be much better to use this to build the normalized table.

score -1 · Answer 2 · answered Apr 25 '18 at 01:58

It's easy to do your purpose IF the data structure is as like as below:

SELECT `membergroupids`, COUNT(`membergroupids`) as 
CountOfMembergroupids FROM `TBL_TEST01` WHERE 1 
GROUP BY `membergroupids`
ORDER BY `userid`

As you mentioned that you have to proceed with large amount of data..., I'd strongly suggest that you could revise your table structure as above...

How to count each item in array with sql

2 Answers2

Create new field with group ids encapsulated by commas