Created September 2016, originally created January 2009.

Using GROUP_CONCAT()

This was a breakthrough for me when I discovered GROUP_CONCAT() back in 2009. I wish I could remember the project better.

SELECT          email                                                                  AS `email` ,
                group_concat( DISTINCT group_name ORDER BY group_name separator ', ' ) AS `groups` ,
                count(votes.id)                                                        AS `# votes`
FROM            users_and_groups , 
                users 
LEFT OUTER JOIN votes 
ON              users.id = votes.user_id 
WHERE           users_and_groups.user_id = users.id 
GROUP BY        users_and_groups.user_id 
ORDER BY        is_admin DESC , 
                groups , 
                email ASC