Getting the SUM of 2 fields and subtracting

Databases
1

Hey,

Users on my site can aqquire points for certain actions. My table is:

member_id | points_count | points_type | points_for | date

points_count are the points that are credited/debited
points_type is either a credit (1) or debit (2).

I had originally just added and subtracted the points in my member table, but now id like to keep track of what the points were credited for.

I tried


SELECT
SUM(credits.points_count) as points_credits, 
SUM(debits.points_count) as points_debits
FROM loyalty_balance as credits
JOIN loyalty_balance as debits ON debits.member_id = '".$_SESSION['kt_login_id']."' AND debits.points_type = '2'
WHERE credits.points_type = '1' AND credits.member_id = ".$_SESSION['kt_login_id']."

$loyalty_points = $row['points_credits']-$row['points_debits'];

Would someone be able to show me the correct statement to achieve this? And if posible do something like


SUM(credits)-SUM(debits) as loyalty_points

Thanks in advance for any help you can provide!

Danny

2 
SELECT points_credits
     , points_debits 
     , points_credits - points_debits AS loyalty_points
  FROM ( SELECT SUM(
                 CASE WHEN points_type = 1
                      THEN points_count 
                      ELSE NULL END ) AS points_credits
              , SUM(
                 CASE WHEN points_type = 2
                      THEN points_count 
                      ELSE NULL END ) AS points_debits 
           FROM loyalty_balance 
          WHERE member_id = ".$_SESSION['kt_login_id']
       ) AS d