CODEWITHSUNDEEP
mysqlsql
user2108863
user2108863

Reputation: 57

mySQL getting a list of unique values within a group by

I have this table 'results':

(name,score,state)  
joe small, 10, NC  
joe small, 5, MN
jane doe, 9, AL

I have this query:
SELECT name, avg(score)
FROM results group by name

I would like to add a new result column of unique states (alphabetical order) related to name to each row of output like so: 

joe small, 5, CA MN NV  
jane doe, 7, AL CA FL

Would I do this with a nested select statement?

Upvotes: 0

Views: 86

Answers (1)

John Conde
John Conde

Reputation: 219834

Use GROUP_CONCAT()

SELECT name, 
    avg(score), 
    GROUP_CONCAT(state SEPARATOR ' ')
FROM results 
GROUP BY name

Upvotes: 4

Related Questions