Convert nulls to zero with SQL

Hello,

I am pulling data through an ODBC, and am returning nulls with the below statement. How can I edit this SQL code so that if it returns a null, it converts it into a zero? I know I can use data cleansing, but it may slow down my process and wanted to have it written in the SQL code.

Sum(P.COGSAmount) as CostOfGoodsSold,

Thank you!

Developer

Accepted answers

kelsey_kincaid

Hi @kvrensanchez ,

You can use a COALESCE statement to set Nulls to Zero.

COALESCE(SUM(P.COGSAmount), 0) as CostOfGoodsSold,

All comments

Raj_Singh1

probably some thing around

if x is null then 0 else y

Raj_Singh1

hi @kvrensanchez

i sually use syntax something like this with case staement

case when field is null then 0 else field end as 'new_field)

so if you writing

select field1, field2, case when field1 is null then 0 else field end as 'new_field_name' from xyz where etc etc

kelsey_kincaid

Hi @kvrensanchez ,

You can use a COALESCE statement to set Nulls to Zero.

COALESCE(SUM(P.COGSAmount), 0) as CostOfGoodsSold,

kvrensanchez

Thank you! This was it.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703