So I need a query to show me how many people are in each type of member-group; We have 50 different membergroups which use approximately 10 or 11 hex colours to sort them by type. For example all of our creative writers are blue (poet, storyteller etc are all the same hex colour).
I want to figure out how many members we have by type and put it into our stats, also so we can figure out who we need to advertise to more or less, and so on.
So in technical terms I need to have a count of how many people are in each hex colour (grouped by hex colour). I think you will need smf_members (id_group), and smf_membergroups (online_colour) but I have no idea how you will put it together. It will be a damn feat of genius!
Are we talking primary or secondary groups?
Primary :D
SELECT COUNT(mem.id_member) AS num_members, mg.online_color
FROM smf_members AS mem
INNER JOIN smf_membergroups AS mg ON (mem.id_group = mg.id_group)
GROUP BY mg.online_color
ORDER BY mg.online_color
That should be the SQL you need. {db_prefix} and $smcFunc optional :P (Also, not a feat of genius. Unless you're calling me a genius. I'm not legitimately called a genius very often.)
THANK YOU!!! That IS legitimate genius, so much better than what I was trying to do ha ha. I can't even explain what I was doing but I didn't even consider this way. So much more efficient, than you!!!
Do you know a way to make the membergroups list in admin group by hex? I don't care of the order as long as they're in bunches, but if you can order it so that the groups get smaller (say 5 have the same hex, then 4, then 2, then 1 then 1 then 1 sort of deal) that would be super awesome. I know I ask for a lot :/.
Doing it to the membergroups list is not recommended because of how the queries already fetch their data.
Ahh okay, thank you! :D