SUBTRACTION between two SUM of rows in one table in one query

Question

I need to show in one table result the SUM of all amount WHERE description= Add Value; SUM all amount WHERE description= Game Credit Used; SUBTRACTION Between SUM Add Value - Game Credit Used. = all this actions only in one table FROM member_transactions WHERE member = ' 32-185149 ';

I tried to write this queries: (SQL that isn't working)

test

SELECT member, description, SUM(amount)  
  FROM member_transactions  
 WHERE member = '32-185149' AND description = 'Add Value'  
UNION  
SELECT member, description,SUM(amount) AS GameCreditUsed  
  FROM member_transactions  
 WHERE member = '32-185149' AND description = 'Game Credit Used';  

need to join SUBTRACTION

SELECT ((SELECT SUM(amount) FROM member_transactions WHERE member = '32-185149' AND description = 'Add Value')  
      - (SELECT SUM(amount) FROM member_transactions WHERE member = '32-185149' AND description = 'Game Credit Used')) 
    AS Balance;

I need to see like this table:

member         description       Result  
32-185149      Add Value         240  
32-185149      Game Credit Used   40  
                                 200  

And I need one query for all actions. Thanks!

Answer 1

Use conditional aggregation:

SELECT member, description,
       SUM(CASE WHEN description = 'Add Value' THEN amount ELSE 0 END) as add_value,
       SUM(CASE WHEN description = 'Game Credit Used' THEN amount ELSE 0 END) as game_credit_used,
FROM member_transactions
WHERE member = '32-185149' ;

You can just subtract the values rather than putting them in separate columns in you want the difference.