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 Project/Vote Poll/Dataset Dummy voter.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.

CodePudding user response：

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.

CodePudding user response：

Another way, (probably a bit more optimal on big data, because aggregation happens over single table):

select country, group_concat(concat(first_name, ' ', last_name, ' x ', c) order by c desc)
from (  
  select country, voter_id, count(*) c 
  from result
  group by country, voter_id
) x
join voter on id = voter_id
group by country;