CODEWITHSUNDEEP
sqlsql-server
user3370649
user3370649

Reputation: 57

Combining rows with NULL columns

select 
    COUNT(Table1.ID) as count_shipped,
    null as count_shipped 
from Table1  
where
    table1.saleStatus='shipped' 
    and table1.saleApproved='yes'

union

select 
    null,
    COUNT(Table1.ID) as count_pending 
from Table1  
where
    table1.saleStatus in ('Pending', 'awaiting payment', 'backorder')

This gives this output 

count_shipped      count_shipped

NULL               5
 4                NULL

but I don't want Null I only want 4 5 in one line Can anybody help me how to do this sql server?

Upvotes: 1

Views: 59

Answers (1)

juergen d
juergen d

Reputation: 204766

You can use a case to sum up your conditions

select sum(case when saleStatus = 'shipped' and table1.saleApproved = 'yes' 
                then 1
                else 0 
           end) as count_shipped,
       sum(case when saleStatus in ('Pending', 'awaiting payment', 'backorder') 
                then 1 
                else 0 
           end) as count_pending 
from Table1

Upvotes: 4

Related Questions