CODEWITHSUNDEEP
sqlsql-server
Jake Wagner
Jake Wagner

Reputation: 826

Return count zero if null

Is there anyway to return 0 when count for a specific column is null. I want to return zero when the count of client_order_id is null instead of the query returning 1.

  select * from data

  client_id            client_order_id
    1                       222
    1                       222
    1                       333
    2                       444
    2                       555
    3
    4

What I have tried:

select client_id,count(distinct client_order_id) as count_of_orders from data
group by client_id
   
 client_id            client_order_id
     1                      2 
     2                      2
     3                      1   
     4                      1

Requested: Any assistance would be appreciated!

 client_id            client_order_id
     1                      2 
     2                      2
     3                      0  
     4                      0

Upvotes: 0

Views: 726

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Your query:

select client_id, count(distinct client_order_id) as count_of_orders
from data
group by client_id;

should be doing what you want. If client_order_id is NULL, then the value should be 0.

So, what looks like a NULL value is not a NULL value. This could occur if the column were a string and the value the empty string. If that is the case, you can use nullif():

select client_id, count(distinct nullif(client_order_id, '')) as count_of_orders
from data
group by client_id;

Upvotes: 1

Related Questions