Edoardo Balestra Edoardo Balestra - 5 months ago 8
SQL Question

MySQL SUM of two rows with condition

I am a beginner in MySQL and I need your help.
I have something like this:

ID Project Type Amount
1 1 I 5000
2 1 D 1000
3 2 I 3500
4 3 I 4300
5 3 D 1200


And I want something like this (the difference between the amounts of I type and D type for the same project; the D type is not always present):

Project Amount
1 4000
2 3500
3 3100

Answer

I suggest using case for taking Amount with + or -:

  select Project as Project,
         sum(case  
               when Type = 'I' then
                 Amount
               when Type = 'D' then 
                -Amount 
             end case) as Amount
    from MyTable
group by Project
--    order by Project -- uncomment this if you want sorted cursor