Untitled

WITH VoteCounts AS (
    SELECT 
        c.first_name,
        c.last_name,
        r.state,
        COUNT(*) as vote_count
    FROM 
        results r
        JOIN candidates c ON r.candidate_id = c.id
    GROUP BY 
        c.first_name, c.last_name, r.state
)
SELECT 
    state,
    GROUP_CONCAT(
        CONCAT(first_name, ' ', last_name, ' x', vote_count)
        ORDER BY vote_count DESC, last_name ASC
        SEPARATOR ', '
    ) as votes
FROM 
    VoteCounts
GROUP BY 
    state
ORDER BY 
    state ASC;