CODEWITHSUNDEEP
sqlarrayspostgresqlgroup-bycount
Rafa Acioly
Rafa Acioly

Reputation: 636

How to count occurences in a list column on postgres?

I've a table with the following structure:

user  |  medias
----------------------
 1    | {ps2,xbox}
 1    | {nintendo,ps2}

How do i count the occurrences of each string in an array column?

Expected result:

media    |  amount
------------------
ps2      |    2
nintendo |    1
xbox     |    1

Upvotes: 2

Views: 180

Answers (1)

GMB
GMB

Reputation: 222432

You can unnest the array with a lateral join, then aggregate:

select x.media, count(*) amount
from myable t
cross join lateral unnest(t.medias) x(media)
group by x.media
order by amount desc, x.media

Upvotes: 1

Related Questions