How will I SUM 2 fields in the same table and subtract them with different criteria?

• Agile

  Ten Centuries

  Points: 1153

  More actions

  April 15, 2009 at 8:50 pm

  #202080

  Hi everyone, can someone help me with this tricky T-SQL, This how it should work.

  Material Qty Status

  A - 100 - 0

  A - 50 - 0

  A - 200 - 1

  A - 50 - 1

  First I need to SUM all Status 1 and also SUM all Status 0 Then I need to find the difference of SUM of Status1 and SUM of Status0

  Sum of Status 0

  100+50 =150

  Sum of Status 1

  200+50 = 250

  Difference

  250-150=100

  Final Output is:

  Material Qty

  A -100

  I really need help on this one. Thanks in advance

  Thanks,
  Morris

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  April 15, 2009 at 8:58 pm

  #978568

  select

  Material,

  sum(case Status when 1 then Qty else 0 end)-

  sum(case Status when 0 then Qty else 0 end) as Qty

  from

  MyTable

  group by

  Material

  order by

  Material

• Agile

  Ten Centuries

  Points: 1153

  More actions

  April 15, 2009 at 9:07 pm

  #978569

  Thank you!!! It was really a big help.:-)

  Thanks,
  Morris