Using case expressions in a calculated column postgresql

Question

Edited to provide sample data I am trying to create a calculated column that sums 2 other columns, which should be easy. However, some values in both of the columns are null, so I want to use a case expression to replace null values in both columns with 0s and then add up the resulting values. The other complicating factor is that the second column contains text values with commas that need to be converted to numerical before I can add them. What I am currently trying to do is:

SELECT (case when pm."PS" is null then 0 else pm."PS" end) + (case when pm."PS-PREV1" is null then 0 else replace(pm."PS-PREV1", ',', '')::numeric end) AS "Sales"
FROM pm

Sample data:

PS	PS-PREV1
20000	null
30000	20,000
null	null
null	30,000

Desired output:

output
20000
50000
0
30000

This is just returning the value of the 1st column without adding in the second column. Where am I going wrong? Am I overthinking this?

Answer 1

your code should work , however you can write it a little bit more clean:

SELECT COALESCE(pm."PS",0) 
       + COALESCE(replace(pm."PS-PREV1", ',', '')::numeric,0) AS "Sales"
FROM pm