Tracking finances owed using a database

For years, I have used the humble spreadsheet as a way of keeping track of finances, but as this spreadsheet grew in size, and as I required more types of data analyses, it eventually became clear that a spreadsheet was no longer going to cut it. Problem is, going from Calc to Base has been a slow learning process in understanding SQL (HSQLDB, specifically), particularly its syntax.

Below is an example table with some arbitrary values. We'll call it Table A. There are a lot of other columns in my original table, but are either irrelevant, or I have already figured out what to do with them.

Quantity	Account Paid From	Recipient A Percentage	Recipient B Percentage
100	A	100	0
200	B	0	100
500	A	0	100
50	B	100	0
10	A	40	60

The idea here is that in row 1, Person A paid for something intended solely for person A. Therefore, this transaction does not result in anyone owing anything to another person. Same with row 2, where person B paid for something intended solely for person B

In row 3, person A paid $500 on behalf of person B. Person B now owes Person A $500.

In row 4, B Paid $50 for A. Subtract 50 from 500, and B now only owes A $450

In row 5, A paid for something that is 40% theirs, and 60% for B. In other words, A paid $6 out of the $10 for B. B now owes A 500 - 50 + 6 $456

I'm looking for something along the lines of the following:

Select all entries where Account Paid From = A
Of those entries, take the total sum of Quantity * Recipient B Percentage / 100
The result is how much B owes A
Select all entries where Account Paid From = B
Of those entries, take the total sum of Quantity * Recipient A Percentage / 100
The result is how much A owes B
Subtract A owes B from B owes A to find out who is in debt to the other (if the value is + or -), and by how much.

I guess something along the lines of:

SELECT SUM("Account Paid From" * "Recipient B Percentage / 100) 
WHERE "Account Paid From" = "A" - SUM("Account Paid From" * "Recipient B Percentage / 100) 
WHERE "Account Paid From" = "B" AS "Owed" 
FROM "Table A"

But...you know, without syntax errors screaming at me.

Solution

For simplicity, I will use table name "T" and the following abbreviated column names:

"Quantity" "Q", "Account Paid From" "APF", "Recipient A Percentage" "RAP", "Recipient B Percentage" "RBP"

SELECT * FROM T WHERE "APF" = 'A'
SELECT SUM("Q" * "RBP" / 100) AS "Sum RBP" FROM "T" WHERE "APF" = 'A'
SELECT SUM("Q" * "RAP" / 100) AS "Sum RAP" FROM "T" WHERE "APF" = 'B'
SELECT "Sum RBP" - "Sum RAP" FROM (SELECT SUM("Q" * "RBP" / 100) AS "Sum RBP" FROM "T" WHERE "APF" = 'A'), (SELECT SUM("Q" * "RAP" / 100) AS "Sum RAP" FROM "T" WHERE "APF" = 'B')