CODEWITHSUNDEEP
sqlpostgresql
Alex
Alex

Reputation: 47

Sum column in postgres

I have a column of varchar() type, value can be any string, is it possible to find sum of all rows that can be cast to numeric?

value
 ----
| 3  |
| 2 2|
| as |
| a1 |
| !2@|
| 0.5|
 ----
 3.5

Upvotes: 1

Views: 65

Answers (2)

Hong Van Vit
Hong Van Vit

Reputation: 2976

May be:

with tb as(

select '3' as v union all
select '2 2' as v union all
select 'as' as v union all
select 'a1' as v union all
select '!2' as v union all
select '0.5' 
)


select sum(case when v ~ '^[0-9\.]+$'
             then v::numeric
             else 0
        end) as result
from tb;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can use a case expression and convert:

select (case when value ~ '^[0-9]+[.]?[0-9]*$'
             then value::numeric
             else 0
        end)
from t;

Here is a db<>fiddle.

Upvotes: 2

Related Questions