求能对一个表内的两个字段做统计的sql语句.
tabel1
iID posID cState
1 2 1
2 2 1
3 2 0
4 3 1
5 3 0
想实现
posID posIDNum cStateNum
2 3 2
3 2 1
也就是按posID分组然后count(posID) 得出posIDNum的值,
cStateNum是 Where cState= '1 '时得出的值.
但cStateNum的值具体怎么得出想不明白,请指点一下.十分感谢!
[解决办法]
create table T(iID int, posID int, cState int)
insert T select 1, 2, 1
union all select 2, 2, 1
union all select 3, 2, 0
union all select 4, 3, 1
union all select 5, 3, 0
select posID,
posIDNum=count(*),
cStateNum=sum(case when cState=1 then 1 else 0 end)
from T
group by posID
--result
posID posIDNum cStateNum
----------- ----------- -----------
2 3 2
3 2 1
(2 row(s) affected)
