CODEWITHSUNDEEP
sqlarrayspostgresqlpostgresql-8.4unnest
Kevin
Kevin

Reputation: 327

I want to count the number of occurences of a value in a string

Given a column namely a which is a result of array_to_string(array(some_column)), how do I count an occurrence of a value from it?

Say I have '1,2,3,3,4,5,6,3' as a value of a column.
How do I get the number of occurrences for the value '3'?

Upvotes: 0

Views: 236

Answers (4)

Evan Carroll
Evan Carroll

Reputation: 1

Based on my "How do you count the occurrences of an anchored string using PostgreSQL?"

The fastest way to count occurrences in a sub-string is this.

SELECT length(data) - length(replace(data, '3', ''))
  / length('3')
FROM foo;

If you're creating the string with array_to_string, it makes sense to unnest, but it won't be faster. Moreover, I would love to see the full query.

count(CASE WHEN some_column=3 THEN 1 END)

sounds the fastest, or if you upgrade to a new version of PostgreSQL..

count(*) FILTER (WHEN some_column=3)

Upvotes: 0

Kevin
Kevin

Reputation: 327

I solved it myself. Thank you for all the ideas! 

SELECT count(something)
FROM unnest(
        string_to_array(
            '1,2,3,3,4,5,6,3'
        , ',')
    ) something
WHERE something = '3'

Upvotes: 1

CodeArt
CodeArt

Reputation: 15

why not just create a for loop() and increment a variable i within an if/else statement for whenever a value == 3

for(var i =0; i<intArray.length;i++){
     if(int[i] = 3){
     var j += 1;

} }

something like that, hopefully you get the idea.

Upvotes: 0

bpinhosilva
bpinhosilva

Reputation: 704

It seems you need to use unnest.

Try this:

select idTable, (select sum(case x when '3' then 1 else 0 end) 
from unnest(a) as dt(x)) as counts 
from yourTable;

Upvotes: 0

Related Questions