Help me! My SQL is broken :(

I've written a little application to help me and my housemates manage who owes whom money. People enter their debts (such as payments for the weekly shopping) into a web interface and it gets logged to a database.

The database has 3 tables:

User { Name, Id }
Expenditure { Pennies, Id, Payer (User.Id) }
Debt { Expenditure (Expenditure.Id), User (User.Id) }

So, a debt from a person to a person enters a row into the "expenditure" tablewith the Id of the lender, and a row into the Debt table for each "Debtor". A weekly shop enters a single row into the expenditure, and then all four housemates as debtors in 4 separate rows (in that case, 1 person is listed as both the lender and a debtor). The amount any one person owes for a particular expenditure is the total expenditure, divided by the number of debtors who are paying it (ie. the number of times it is reference in the Debts table)

Hopefully that makes sense!

My problem is writing a bit of SQL to calculate who owes what. I want to write a query which calculates the total debt from person to person, this needs to get all the expenditures which reference the lender and all the debts which reference the debtor and (vitally) it needs to sum the pennies of all the expenditures divided by the number of debts which reference it.

I can then calculate the total outstanding debt simply through:

Debt(A, B) - Debt(B, A)

At the moment I have this SQL:

SELECT 
    SUM("Pennies") / (SELECT COUNT(*) FROM public."Debt", public."Expenditure" WHERE public."Expenditure"."Id" = public."Debt"."Expenditure") As "Refs"
FROM
    public."Debt",
    public."Expenditure"
WHERE
    public."Debt"."User" = $debtorId
AND
    public."Expenditure"."Payer" = $lenderId
AND
    public."Expenditure"."Id" = public."Debt"."Expenditure"

which looks right, but when I add the values up by hand I get different 开发者_如何转开发numbers.

EDIT:: In response to one of the answers below. I have the number of shares being displayed, but it always seems to display 1 :(

SELECT 
    shares
FROM
    public."Debt",
    public."Expenditure",
    (SELECT COUNT(*) as "shares", public."Expenditure"."Id" as "Id" FROM public."Expenditure" GROUP BY public."Expenditure"."Id") as "debtors"
WHERE
    public."Debt"."User" = 4
AND
    public."Expenditure"."Payer" = 1
AND
    public."Expenditure"."Id" = public."Debt"."Expenditure"
AND
    "debtors"."Id" = public."Debt"."Expenditure"

Double check the SQL you have for calculating the number of people owing a debt - your current code calculates the total number of portions owing on all debts in the system.

That sub-select should probably be moved into the FROM section

(SELECT Debt.Id , COUNT(*) as shares FROM Debt INNER JOIN Expenditure on Debt.Expenditure=Expenditure.Id GROUP BY Debt.Id) as debtors

and add a where clause -

AND debtors.Id = Expenditure.Id

Then you can

SELECT SUM(Pennies/shares) ...

I don't understand the part "divided by the number of debts which reference it." What you actually do is dividing by the total number of Depts in your database (provided that they all have an Expenditure).