求一SQL语句
表   A
--------------------------------
Number     Status
  1                 Y
  1                 N
  2                 Y
  2                 Y
-------------------------------

要求得到统计记录如下
--------------------------
Number       Count   (记录数）       CountY   (为Y的记录数）          
    1                     2                             1
    2                     2                             2
          (汇总）   4                             3



------解决方案--------------------
select number,count(*) as count,sum(case when status= 'y ' then 1 else 0 end) as county from tablename group by number
union all
select number= ' ',count(*) as count,sum(case when status= 'y ' then 1 else 0 end) as county from tablename
------解决方案--------------------
declare @tabname table (iid int, iname varchar(10))
insert into @tabname(iid,iname)
select 1 , 'Y '
union all
select 1 , 'N '
union all
select 2 , 'Y '
union all
select 2 , 'Y '
select isnull(cast(iid as varchar), '(汇总) '),count(*),inumber=sum(case iname when 'Y ' then 1 else 0 end) from @tabname group by iid with rollup



------解决方案--------------------
--建表
create table a
(
number int,
status varchar(1)
)

insert into a select 1, 'Y '
insert into a select 1, 'N '
insert into a select 2, 'Y '
insert into a select 2, 'Y '

--语句
select isnull(cast(number as varchar), '汇总 ') as number,count(1) as [count],
sum(case status when 'y ' then 1 end) as county
from a
group by number with ROLLUP

--结果
1 2 1
2 2 2
汇总 4 3

--删除环境
drop table a