SQL subtracting results of 2 select statement

Question

I have two queries that work perfectly fine in isolation:

SELECT patient.patient_id, sum(cost)
FROM patient, appointment 
WHERE patient.patient_id = appointment.patient_id 
GROUP BY patient.patient_id

---

SELECT sum(payment_amount)
FROM patient, payment
WHERE patient.patient_id = payment.patient_id
GROUP BY patient.patient_id

What I would like to know is how I might subtract the results of the second query from the first to create a current_balance field in my patient table?

Answer 1

Roll up both quires into one and then deduct the SUMs.

SELECT pat.patient_id, SUM(cost) SUMCOST, SUM(payment_amount) SUMpayment_amount, SUM(cost) - SUM(payment_amount) current_balance 
FROM patient pat
INNER JOIN payment pay ON pat.patient_id = pay.patient_id 
INNER JOIN appointment app ON pat.patient_id = app.patient_id
GROUP BY pat.patient_id