select [uId]
  ,[uName] 
,(case when [p1_weiBo]='Yes' then 1 else 0 end)+
(case when [p2_weiBo]='Yes' then 1 else 0 end)+
(case when [p3_weiBo]='Yes' then 1 else 0 end)+
(case when [p4_weiBo]='Yes' then 1 else 0 end)+
(case when [p5_weiBo]='Yes' then 1 else 0 end) as NUM
from Table1

------解决方案--------------------

SQL code
select
  sum(case weiBo when 'yes' then 1 else 0 end) as 数量
from
  (
   select  p1_weiBo from Table1
   union all
   select  p2_weiBo from Table1
   union all
   select  p3_weiBo from Table1
   union all
   select  p4_weiBo from Table1
   union all
   select  p5_weiBo from Table1
)t