Counting distinct elements from strings PostgreSQL

Question

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.

user330315 · Accepted Answer · 2016-11-07 11:30:12Z

2

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;

answered Nov 7, 2016 at 11:30

user330315

Sign up to request clarification or add additional context in comments.

Comments

Boris Schegolev · Accepted Answer · 2016-11-07 11:31:39Z

0

I used regexp_split_to_table:

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

answered Nov 7, 2016 at 11:31

Boris Schegolev

3,7115 gold badges23 silver badges35 bronze badges

Collectives™ on Stack Overflow

Counting distinct elements from strings PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related