想统计一个表中的记录,对于某条字段Money超过10的只统计条数,不统计Money的总数:
如下表(B为Money):
A B
1 5
2 3
3 5
4 12
5 2
6 11
7 1
统计最后想得到结果是:
count(*) sum(B)
7 16
即记录数还是7,但sum(B)中不计算B超过10的记录
如下表(B为Money):
A B
1 5
2 3
3 5
4 12
5 2
6 11
7 1
统计最后想得到结果是:
count(*) sum(B)
7 16
即记录数还是7,但sum(B)中不计算B超过10的记录
from tbl
declare @t table(A int,B int)
insert into @t select 1,5
union all select 2,3
union all select 3,5
union all select 4,12
union all select 5,2
union all select 6,11
union all select 7,1select count(1) as [count(*)],
sum(case when B>10 then 0 else B end) as [sum(B)]
from @t
FROM table1