MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.27


CREATE TABLE voter
	(ID INT,
    first_name VARCHAR(10),
    last_name VARCHAR (10));

CREATE TABLE result(
	voter_id INT,
    country VARCHAR(10));
    

INSERT INTO voter(ID, first_name, last_name)
VALUES
	(1,'charlie','puth'),
    (2,'michael','jackson'),
    (3,'nicky','minaj');

INSERT INTO result(voter_id, country)
VALUES
	(1,'California'), 
    (1,'Texas'), 
    (2,'Mexico'), 
    (1,'Nevada'),
    (3,'Washington'),
    (2,'California'), 
    (3,'Mexico'),
    (3,'Texas'), 
    (1,'California'), 
    (2,'Mexico'); 
-- your query:
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;
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_1030124635.v.first_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

-- GROUP BY should contain all fields that are not used in an aggregate function:
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;


country
votes


California
charlie puth x 2

Texas
charlie puth x 1

Mexico
michael jackson x 2

Nevada
charlie puth x 1

Washington
nicky minaj x 1

California
michael jackson x 1

Mexico
nicky minaj x 1

Texas
nicky minaj x 1


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;


country
votes


California
charlie puth x 2,,michael jackson x 1,

Mexico
michael jackson x 2,,nicky minaj x 1,

Nevada
charlie puth x 1,

Texas
charlie puth x 1,,nicky minaj x 1,

Washington
nicky minaj x 1,

country	votes
California	charlie puth x 2
Texas	charlie puth x 1
Mexico	michael jackson x 2
Nevada	charlie puth x 1
Washington	nicky minaj x 1
California	michael jackson x 1
Mexico	nicky minaj x 1
Texas	nicky minaj x 1

country	votes
California	charlie puth x 2,,michael jackson x 1,
Mexico	michael jackson x 2,,nicky minaj x 1,
Nevada	charlie puth x 1,
Texas	charlie puth x 1,,nicky minaj x 1,
Washington	nicky minaj x 1,