Combining rows with NULL columns

Question

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?

juergen d · Accepted Answer · 2014-12-20 11:39:40Z

4

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

answered Dec 20, 2014 at 11:39

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Combining rows with NULL columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related