CODEWITHSUNDEEP
stringpostgresqlcount
johan855
johan855

Reputation: 1626

Counting distinct elements from strings PostgreSQL

I am trying to count the elements contained in a string in the following way:

row  features
1    'a | b | c'
2    'a | c'
3    'b | c | d'
4    'a'

Result:

feature  count
a        3
b        2
c        3
d        1

I have already found a solution by finding the highest number of features and separating each feature into 1 column, so feature1 = content of feature 1 in the string, but the I have to manually aggregate the data. There must be a smart way to do this for sure as in my example.

Upvotes: 0

Views: 126

Answers (2)

Boris Schegolev
Boris Schegolev

Reputation: 3701

I used regexp_split_to_table:

SELECT regexp_split_to_table(feature, E' \\| ') AS k, count(*)
FROM tab1
GROUP BY k

Upvotes: 0

user330315
user330315

Reputation:

By normalizing the data by using unnest() this turns into a simple group by

select trim(f.feature), count(*) 
from the_table t
  cross join lateral unnest(string_to_array(t.features, '|')) as f(feature)
group by trim(f.feature)
order by 1;

Upvotes: 2

Related Questions