SQL - Contar cantidad de columnas que no sean cero (0)

create table #temp

(

phone varchar(20),

dia1 int,

dia2 int,

dia3 int,

dia4 int

)

insert into #temp values ('987654321' , 0 , 1 , 5 , 0 )

insert into #temp values ('321654998' , 1 , 2 , 3 , 0 )

insert into #temp values ('456789123' , 1 , 1 , 1 , 1 )

insert into #temp values ('111111111' , 0 , 1 , 0 , 1 )

insert into #temp values ('222222222' , 0 , 0 , 0 , 1 )

select t3.phone,dia1,dia2,dia3,dia4,total from #temp t3

left join (select phone, (isnull(tot_dia1,0)+isnull(tot_dia2,0)+isnull(tot_dia3,0)+isnull(tot_dia4,0)) total from

(

select

phone,

case

when dia1>0 then count(1) end as tot_dia1,

case

when dia2>0 then count(1) end as tot_dia2,

case

when dia3>0 then count(1) end as tot_dia3,

case

when dia4>0 then count(1) end as tot_dia4

from #temp

group by phone,dia1,dia2,dia3,dia4

) t1 group by phone,tot_dia1,tot_dia2,tot_dia3,tot_dia4) t2 on (t3.phone=t2.phone)