求一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
--结果
121
222
汇总43
--删除环境
drop table a
