I have a problem when I use left join and group by running this code:
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]