求一SQL语句,有点难度,希望高手来看看,谢谢!

[gshope]

表结构:
name types
a 1
a 0
b 0
b 0
c 1
c 0
查询结果是:按name分组,显示不同name的总数和types是1的记录数,如下:

name 总记录数 types是1的记录数
a 2 1
b 2 0
c 2 1
希望用一句SQL解决,不用存储过程.

[Andy__Huang]

select name,总记录数=count(*),types是1的记录数=sum(types)
from 表
group by name

[gshope]

to hdhai9451(新新人类)
老大,您也太巧了吧?我是举个例子,types也许是其它形式,或者记录比较多,不是正好0+1=1的情况!!

[td427]

select name,count(*) as RecordTotal,sum(types) as TypesTotal
from table
group by name

[子陌红尘]

select
name,
count(*) as [总记录数],
sum(case types when 1 then 1 else 0 end) as [types是1的记录数]
from
表
group by
name


或者投机一点：

select
name,
count(*) as [总记录数],
sum(types) as [types是1的记录数]
from
表
group by
name

[paoluo]

那这么写

Select
name,
Count(1) As N'总记录数',
SUM(Case types When 1 Then 1 Else 0 End) As N'types是1的记录数'
from TableName
Group By name
Order By name

[paoluo]

这样types 为1，2都可以计算出来了。

[子陌红尘]

--生成数据
create table #t(name varchar(10),types int)
insert into #t select 'a',1
insert into #t select 'a',0
insert into #t select 'b',0
insert into #t select 'b',0
insert into #t select 'c',1
insert into #t select 'c',0


--执行查询
select
name,
count(*) as [总记录数],
sum(case types when 1 then 1 else 0 end) as [types是1的记录数]
from
#t
group by
name
order by
name


--输出结果
name 总记录数 types是1的记录数
---- -------- ----------------
a 2 1
b 2 0
c 2 1

[小李木耳]

select a.name,a.总记录数,b.types是1的记录数 from
(select name,count(*) as 总记录数
from 表
where types='1'
group by name
) a,

(select name,count(*) as types是1的记录数
from 表
group by name
) b

where a.name=b.name

[td427]

select [name],a.counts,count(a.types) as c1
from
(select name,count(*) as counts,types from [temp]
group by name,types) as a where a.types = 1
group by name,a.counts

SQL查询分析器中调试通过

[小李木耳]

这种数据你也能同的过?好像不行吧！？
name types
a 1
a 0
> a 1
> a 2
b 0
b 0
c 1
c 0