CODEWITHSUNDEEP
sqlarrayspostgresqlselectgroup-by
arezoo
arezoo

Reputation: 338

How canI aggregate and group in two columns?

   Name          age   gender
competition1      3      0
competition1      3      1
competition1      2      0
competition1      2      1
competition2      3      0
competition2      3      1
competition3      1      0

I want to query in this table that convert in below table.

     Name          Array of ages   Array of gender
'competition1',      {'3','2'}       {'0','1'}
'competition2',      {'3'}       {'0','1'}
'competition3',      {'1'}           {'0'}

Upvotes: 0

Views: 27

Answers (2)

GMB
GMB

Reputation: 222382

You can aggregate by name, and use array_agg(distinct ...) to generate arrays without duplicates. Note that you can also order the values in the arrays as needed:

select 
    name, 
    array_agg(distinct age order by age desc) ages, 
    array_agg(distinct gender order by gender) genders
from mytable
group by name

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You could use array_agg 

 SELECT name, array_agg(age), array_agg(gender)
 FROM data_table
 GROUP BY name

Upvotes: 0

Related Questions