Need help with an SQL query that's probably really easy but I'm an idiot :D

Started by Jade Elizabeth, August 16, 2014, 09:41:01 PM

Previous topic - Next topic

Jade Elizabeth

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!
Once proud Documentation Writer and Help Squad Leader | Check out my new adult coloring career: Color With Jade/Patreon.

Arantor


Jade Elizabeth

Once proud Documentation Writer and Help Squad Leader | Check out my new adult coloring career: Color With Jade/Patreon.

Arantor

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.)

Jade Elizabeth

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!!!
Once proud Documentation Writer and Help Squad Leader | Check out my new adult coloring career: Color With Jade/Patreon.

Jade Elizabeth

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 :/.
Once proud Documentation Writer and Help Squad Leader | Check out my new adult coloring career: Color With Jade/Patreon.

Arantor

Doing it to the membergroups list is not recommended because of how the queries already fetch their data.

Jade Elizabeth

Once proud Documentation Writer and Help Squad Leader | Check out my new adult coloring career: Color With Jade/Patreon.

Advertisement: