CODEWITHSUNDEEP
sqlpostgresql
reactdto2
reactdto2

Reputation: 123

Calculate the amount money using SQL

I want to count how much user ID 1 has money on their account. I don't have a column with the amount of money because I want to keep simple Double-entry bookkeeping.

So I would like to calculate it based on the transactions made on the SQL operation in my PostgreSQL database. My table looks similar to this:

  id  sender_id    recipient_id           amount_money
  --- -----------  ---------------------- -----------------
  1   1            2                      60.00
  2   1            2                      15.00
  3   2            1                      35.00

If the user is a sender, the amount should be subtracted. Else if the user is a recipient, the amount should be added.

So based on the available table, user id 1 should have: -40.00 and user id 2 should have: 40.00.

How can I do this on PostgreSQL, providing only the user's id number in the argument? I can't program in SQL so well that I can handle it myself.

btw, is it okay to do this with SQL? My application backend is written in NodeJS. I did a small analysis and decided that my way of saving money would be fine, but I'm just a programmer with one year's experience.

Upvotes: 2

Views: 277

Answers (1)

forpas
forpas

Reputation: 164089

With conditional aggregation:

select sum(case when recipient_id = 2 then 1 else -1 end * amount_money) total
from tablename
where 2 in (sender_id, recipient_id)

See the demo.
Results:

| total |
| ----- |
| 40.00 |

Upvotes: 2

Related Questions