CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
Daryl McCullough
Daryl McCullough

Reputation: 301

SQL group values for one column by another column

Is there some kind of "aggregate" function in SQL that just turns values into a list? An example might be a table of the form:

| game_id | player | score |
|---------|--------|-------|
|   1     | fred   | 2     |
|   1     | tom    | 1     |
|   2     | fred   | 3     |
|   2     | tom    | 4     |

What I would like returned is a table that looks like this:

| player | scores |
|--------|--------|
|   fred | 2, 3   | 
|   tom  | 1, 4   |

The command might look something like: SELECT player, listify(score) FROM games GROUP BY player;

Upvotes: 2

Views: 4740

Answers (2)

Shivam Sharma
Shivam Sharma

Reputation: 1597

You can use string_agg so then you'll not need to call 2 different functions array_agg and array_to_string.

SELECT player, string_agg(score::text, ',') AS scores FROM games GROUP BY player;

STRING_AGG()

Live Demo

Upvotes: 2

Daryl McCullough
Daryl McCullough

Reputation: 301

Thanks to Tim and Gordon. The Postgres function I'm looking for is array_agg. The full query in this case looks like this:

SELECT player, array_to_string(array_agg(score), ',') AS scores FROM games GROUP BY player;

array_agg puts all the scores into an array, and you have to convert them to a string to be able to return them in a SELECT statement.

Upvotes: 4

Related Questions