Xtykutl

I have a transaction table set up like this:

-- Transactions table

+----+---------+-------+------------------+--------+-----------+

| id | from_id | to_id | transaction_type | amount | card_type |

+----+---------+-------+------------------+--------+-----------+

| 1  | 1       | 1     | deposit          | 90     | debit     |

| 2  | 1       | 2     | transfer         | -60    | debit     |

| 3  | 2       | 2     | deposit          | 10     | debit     |

| 4  | 2       | 2     | deposit          | 20     | credit    |

+----+---------+-------+------------------+--------+-----------+

If i deposit it should show a positive value to show that money was added to my account, but if i do a transfer it should use a negative balance to show that money was removed from my account. The issue is, I can't think of a query that would add the money to the user 2 account from the transfer of user 1 to produce a view like this (based on card type):

-- Debit Balance Table

+---------+---------+

| user_id | balance |

+---------+---------+

| 1       | 30      |

| 2       | 70      |

+---------+---------+



-- Credit Balance Table

+---------+---------+

| user_id | balance |

+---------+---------+

| 1       | 0       |

| 2       | 20      |

+---------+---------+

I know you can't add money to a credit account but just forget that logic for now.

For debit, you can simply do conditional aggregation:

SELECT 

  all_users.user_id, 

  SUM (CASE 

         WHEN t.transaction_type = 'deposit' AND all_users.user_id = t.from_id

           THEN ABS(t.amount) 

         WHEN t.transaction_type = 'transfer' AND all_users.user_id = t.from_id

           THEN -ABS(t.amount)

         WHEN t.transaction_type = 'transfer' AND all_users.user_id = t.to_id

           THEN ABS(t.amount)

         ELSE 0

       END

      ) AS balance 

FROM transactions AS t 

JOIN (

      SELECT from_id AS user_id FROM transactions 

      UNION 

      SELECT to_id FROM transactions

     ) AS all_users 

  ON t.from_id = all_users.user_id OR 

     t.to_id = all_users.user_id 

WHERE t.card_type = 'debit' 

GROUP BY all_users.user_id