How to fetch who voted and how many votes were given to each country using MySQL?

I need help with query how to fetch who voted and how many votes were given to each country using MySQL?

So, i have table voter and result, you can find the dummy dataset here https://github.com/FirzaCank/Project/blob/main/SQL/Self%20Project/Vote%20Poll/Dataset%20Dummy%20voter.sql

Which voter table contains :

1. ID (INT)
2. first_name VARCHAR(10)
3. last_name VARCHAR(10)

and result table contains :

1. voter_id (INT)
2. country VARCHAR(10)

I’ve tried mysql query like this :

SELECT
country,
CONCAT(v.first_name,' ',v.last_name,' x ',COUNT(r.voter_id)) AS votes
FROM voter v
RIGHT JOIN result r ON v.id = r.voter_id
GROUP BY country;

But i got error, i’m sure this problem need kinda things like looping and i don’t really understand that stuff.

The desire output is will be like this, but as far as i’ve tried in the above syntax it just came out with my output which i can’t display all voter in the same country, i just came out with 1 voter every 1 country.

>Solution :

SELECT
    country,
    group_concat(votes,',') as votes
FROM
(
   SELECT
      country,
      CONCAT(v.first_name,' ',v.last_name,' x ',COUNT(r.voter_id)) AS votes
   FROM voter v
   RIGHT JOIN result r ON v.id = r.voter_id
   GROUP BY country,first_name,last_name
) x
GROUP BY country;

see: DBFIDDLE

First your query give an error. This one:

"SELECT list is not in GROUP BY clause and contains nonaggregated
column ‘…first_name’ which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by"

This error, and how to handle it, is epxlained here: MySQL Handling of GROUP BY

This means that all fields that are not used in an aggregate function, you be mentioned in the GROUP BY.

Because your desire is that the output only contains 1 line per country the GROUP_CONCAT function is added on the result.