sql server - How to display the correct sum() value based on the type -

table 1          staff_no   type      amount   a1         o         300     b3                  10     a1                  45     c3         o          70     c4                 300     d4         o         100     table2   staff_no    code  a1          ss1  b3          ss1  c3          ss2  c4          ss2  d4          ss2 

i inner join 2 tables , select code, headcount , sum @ both type 'o' , 'a'based on code. here sql statement:

select distinct b.code,  count(distinct b.code) headcount,  o=(select sum(amount)  table1 type ='o'),    a=(select sum(amount)  table1 type ='a') table1 inner join table2 b on a.staff_no=b.staff_no group b.code 

my error: ss1 , ss2 on 'o' , 'a' have same value.

code    headcount   o   ss1         2     300   55 ss2         3     300   55 

the result should get:

code    headcount   o   ss1        2      300   55 ss2        3      170   300 

group by friend.

select   b.code,   count(a.staff_no) headcount,   sum(case a.type when 'o' a.amount else 0 end) o,   sum(case a.type when 'a' a.amount else 0 end)   table1    inner join table2 b on b.staff_no = a.staff_no group   b.code 

if a.amount can contain null, use isnull:

sum(case a.type when 'o' isnull(a.amount, 0) else 0 end) o