SELECT merchant_no,
COUNT(CASE WHEN trans_type=1 THEN 1 END) [trans_type=1的个数],
COUNT(CASE WHEN trans_type=2 THEN 1 END) [ trans_type=2的个数]
FROM tb
GROUP BY merchant_no
COUNT(CASE WHEN trans_type=1 THEN 1 END) [trans_type=1的个数],
COUNT(CASE WHEN trans_type=2 THEN 1 END) [ trans_type=2的个数]
FROM tb
GROUP BY merchant_no
sum(case trans_type when 1 then 1 else 0 end) 'trans_type=1的个数',
sum(case trans_type when 2 then 1 else 0 end) 'trans_type=2的个数'
from line
group by merchant_no
select [merchant_no],
count1=sum(case [trans_type] when 1 then 1 else 0 end),
count1=sum(case [trans_type] when 2 then 1 else 0 end)
from @line a
group by [merchant_no]--Result
/*
merchant_no count1 count1
----------- ----------- -----------
111 1 2
222 0 1
*/
select
a.merchant_no,
trans_type_1 = sum(case when trans_type = 1 then 1 else 0 end),
trans_type_2 = sum(case when trans_type = 2 then 1 else 0 end)
from line a
group by a.merchant_no
insert into line values(111 , 1 )
insert into line values(111 , 2 )
insert into line values(111 , 2 )
insert into line values(222 , 2 )
select merchant_no,
sum(case trans_type when 1 then 1 else 0 end) 'trans_type=1的个数',
sum(case trans_type when 2 then 1 else 0 end) 'trans_type=2的个数'
from line
group by merchant_no--drop table line/*
merchant_no trans_type=1的个数 trans_type=2的个数
----------- --------------- ---------------
111 1 2
222 0 1(所影响的行数为 2 行)*/
,sum(case when trans_type=1 then 1 else 0 end) [trans_type=1的个数]
,sum(case when trans_type=2 then 1 else 0 end) [trans_type=2的个数]
from line group by merchant_no
insert into line values(111 , 1 )
insert into line values(111 , 2 )
insert into line values(111 , 2 )
insert into line values(222 , 2 )
select merchant_no,
sum(case trans_type when 1 then 1 else 0 end) 'trans_type=1的个数',
sum(case trans_type when 2 then 1 else 0 end) 'trans_type=2的个数'
from line
group by merchant_no--drop table line/*
merchant_no trans_type=1的个数 trans_type=2的个数
----------- --------------- ---------------
111 1 2
222 0 1(所影响的行数为 2 行)*/
select merchant_no,
sum( abs(abs(sign(isnull(trans_type,0)-1))-1) ) count1,
sum( abs(abs(sign(isnull(trans_type,0)-2))-1) ) count2
from line group by merchant_no