I have a problem when I use left join and group by running this code:
x
select a.[fromAccCode]
,a.[CenterCode]
, CASE WHEN b.CFCUSTYP = 0 THEN 'm' ELSE 'n' END AS person
,sum(a.[value]) 'sumValue'
from [dbo].[tmp_dep_ex] a left join [dbo].[5_CBCIF] b
on a.[CFCIFNO]=b.[CFCIFNO]
group by a.[CenterCode]
,a.[fromAccCode]
,b.CFCUSTYP
order by
a.[CenterCode]
,a.[fromAccCode]
,b.CFCUSTYP
The result is:
Result is
fromAccCode | CenterCode | person | sumValue
201443 | 3/2/0160 | m | 1707632873
201443 | 3/2/0160 | n | 8723822181
201443 | 3/2/0160 | m | 173260000
How to change this result to:
fromAccCode | CenterCode | person | sumValue
201443 | 3/2/0160 | m | 1880892873
201443 | 3/2/0160 | n | 8723822181
Thanks for reading my question.
Advertisement
Answer
you need to add CASE WHEN b.CFCUSTYP = 0 THEN 'm' ELSE 'n' END
in group by clause
select a.[fromAccCode]
,a.[CenterCode]
, CASE WHEN b.CFCUSTYP = 0 THEN 'm' ELSE 'n' END AS person
,sum(a.[value]) 'sumValue'
from [dbo].[tmp_dep_ex] a left join [dbo].[5_CBCIF] b
on a.[CFCIFNO]=b.[CFCIFNO]
group by a.[CenterCode]
,a.[fromAccCode]
,CASE WHEN b.CFCUSTYP = 0 THEN 'm' ELSE 'n' END
order by
a.[CenterCode]
,a.[fromAccCode]