select 科室=ghks,挂号人次=count(*),退号人次=sum(case when thbz=1 then 1 else 0 end),
实际人次=sum(case when thbz=0 then 1 else 0 end)
from tbName
group by ghks

------解决方案--------------------
select ghks 科室 , count(*) 挂号人次 , 
 sum(case thbz when  '1 ' then 1 end)  '退号人次 ',  
 sum(case thbz when  '0 ' then 1 end)  '实际人次 '  
from tb  
where convert(varchar(7),ghrq,120) =  '2007-09 '  
group by ghks  


------解决方案--------------------
--注意:将''中的空格删除.
select ghks 科室 , count(*) 挂号人次 ,  
 sum(case thbz when '1' then 1 else 0 end) '退号人次', 
 sum(case thbz when '0' then 1 else 0 end) '实际人次' 
from tb 
where convert(varchar(7),ghrq,120) =   '2007-09' 
group by ghks 


------解决方案--------------------
SQL code

select 挂号科室,
[挂号人次]=(select count(*) from table1 where convert(varchar(6),挂号日期,112)='200709')
[退号人次]=sum(case 退号标志 when 1 then 1 else 0 end),
[退号人次]=sum(case 退号标志 when 0 then 1 else 0 end)
from 
table1
where
convert(varchar(6),挂号日期,112)='200709'--取年月
group by 挂号科室