SELECT ug.title, ug.user_group_id, IF(ugr.is_primary = 1, 'PRIMARY', 'SECONDARY') AS type, COUNT(*) AS numUsers
FROM xf_user_group_relation AS ugr
LEFT JOIN xf_user_group AS ug ON (ug.user_group_id = ugr.user_group_id)
GROUP BY ugr.user_group_id, ugr.is_primary;