Limiting query module results by member group_id

Question

I am trying to limit the following query by the member's group id, but having some difficulty with the join statement.

This query works fine:

{exp:query sql="SELECT count(*) as member_count, m_field_id_6 as state
FROM exp_member_data WHERE m_field_id_6 != ''
AND m_field_id_9 = 'USA'
GROUP BY m_field_id_6 ORDER BY m_field_id_6 ASC
"}
  {state} ({member_count})<br />
{/exp:query}

But I am struggling with how to limit by group_id:

{exp:query sql="SELECT count(*) as member_count, m_field_id_6 as state
FROM exp_member_data WHERE m_field_id_6 != ''
AND m_field_id_9 = 'USA'
JOIN exp_members ON exp_members.member_id = exp_member_data.member_id
WHERE exp_members.group_id='5'
GROUP BY m_field_id_6 ORDER BY m_field_id_6 ASC
"}
  {state} ({member_count})<br />
{/exp:query}

The end result would be a directory of members grouped by state. I've maxed out my query knowledge, thank you for any assistance or suggestions.

Answer 1

You can try this query:

{exp:query sql="SELECT count(*) as member_count, m_field_id_6 as state
        FROM exp_member_data 
        JOIN exp_members ON exp_members.member_id = exp_member_data.member_id
        WHERE m_field_id_6 != '' 
        AND m_field_id_9 = 'USA'
        AND exp_members.group_id='5'
        GROUP BY m_field_id_6 ORDER BY m_field_id_6 ASC"}
    {state} ({member_count})<br />
{/exp:query}

Plz share if ORDER BY is also working fine.