select ucTa, sTr, sRe ,stPart,
sum(case when error in (43,44) then 1 else 0 end),
sum(case when error in (43,44) then 0 else 1 end)from tb1
group by ucTa, sTr, sRe ,stPart
order by 1,2,3
sum(case when error in (43,44) then 1 else 0 end),
sum(case when error in (43,44) then 0 else 1 end)from tb1
group by ucTa, sTr, sRe ,stPart
order by 1,2,3
insert #t select 39765,'9/7/2005 14:33:47',2048,179010289,3,12,3
union all select 39765,'9/7/2005 14:32:47',43,179010289,2,12,1
union all select 39765,'9/7/2005 14:31:47',44,179011231,1,25,3
union all select 39765,'9/7/2005 14:28:47',4535,179012319,4,22,1
union all select 39765,'9/7/2005 14:27:47',4545,179010289,4,34,1
union all select 39715,'9/7/2005 14:25:47',6766,179012332,2,37,2
union all select 39715,'9/7/2005 14:23:47',1454,179010289,1,25,3
union all select 39715,'9/7/2005 14:22:47',44,179011211,1,25,1
union all select 39715,'9/7/2005 14:16:47',3434,179010289,3,25,2select ucTa,_sTr,sRe,
cast((sum(case lError when 43 then 1 else 0 end)+sum(case lError when 44 then 1 else 0 end)) as varchar(10)) [44 or 43],
cast((sum(case lError when 43 then 0 else 1 end)+sum(case lError when 44 then 0 else 1 end)) as varchar(10)) [except 43 or 44]
,strPart from #t
group by ucTa,_sTr,sRe,strPart
order by ucTa,_sTr,sRe
drop table #t
/*
ucTa _sTr sRe 44 or 43 except 43 or 44 strPart
----------- ----------- ----------- ---------- --------------- --------------------
1 25 1 1 1 179011211
1 25 3 0 2 179010289
1 25 3 1 1 179011231
2 12 1 1 1 179010289
2 37 2 0 2 179012332
3 12 3 0 2 179010289
3 25 2 0 2 179010289
4 22 1 0 2 179012319
4 34 1 0 2 179010289(9 row(s) affected)
*/
is this ok???