CODEWITHSUNDEEP
sqlsql-servert-sqlcounting
cmpmd2
cmpmd2

Reputation: 165

TSQL Counting with Nulls

I have a table what counts admited and discharge from ED. This is what the table looks like

enter image description here

I'm trying to get the total admitted and discharged. Something like

Admitted - 100 Discharge - 200

Is there a way to do that with the NULL values?

Upvotes: 1

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Just use sum();

select sum(admitted) as admitted, sum(DischargedFromED) as DischargedFromED
from t;

Aggregation functions ignore NULL values.

If you are concerned about NULL values appearing after the sum(), then use coalesce() afterwards:

select coalesce(sum(admitted), 0) as admitted,
       coalesce(sum(DischargedFromED), 0) as DischargedFromED
from t;

The above assumes that the columns are numeric. If they are some other type, they need to be converted to numbers.

Upvotes: 2

Josh
Josh

Reputation: 3477

select 
    sum(case when Admitted is null then 0 else Admitted end) Admitted,    
    sum(case when DischargedFromED is null then 0 else DischargedFromED end) as DischargedFromED
from MyMagicalTable;

or

select
    sum(coalesce(Admitted, 0)) Admitted,
    sum(coalesce(DischargedFromED, 0)) DischargedFromED
from MyMagicalTable;

Upvotes: 5

Related Questions