Selecting an atrribute when performing grouping by another attribute

user_13413328 0
Viewed 31

I have the following table named Pokemon that has info about some pokemons.

+------------+-------+--------+--------+--+
|    name    | type  | weight | color  |  |
+------------+-------+--------+--------+--+
| bulbasaur  | grass |     10 | green  |  |
| squirtle   | water |      7 | blue   |  |
| charmander | fire  |      8 | orange |  |
| cyndaquil  | fire  |      4 | black  |  |
| staryu     | water |     12 | orange |  |
+------------+-------+--------+--------+--+

I would like to group this table by color and select the name of each pokemon belonging to each color group.

Namely I want 4 groups :

  1. green
  2. blue
  3. orange
  4. black

and the names of each pokemon in each group:

  1. bulbasaur
  2. squirtle
  3. charmander,staryu
  4. cyndaquil

I have tried using the GROUP BY clause in SQL like so:

SELECT name
FROM POKEMON
GROUP BY color

but that does not work since the GROUP BY clause insists that the column attribute in SELECT must either be involved in the GROUP BY or have an aggregation function act on it.

Since it is my third day coding in SQL I have not found a solution to this problem yet.

Thank you in advance for your time trying to help.

aggregate-functions postgresql sql string
2 Answers

You want string aggregation. In Postgres, you would phrase this as:

select color, string_agg(name, ',') as names
from pokemon
group by color
user_106767160

You need to also select the color, such as :

SELECT name, color
FROM POKEMON
GROUP BY name, color
user_118189940
Related