Skip to content
Advertisement

how to sum 2 record when using left join in sql server?

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]
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement