SQL calculation (Oracle)

I have two db tables Fine and Fine_payment. Fine has fields

* fine_id[pk开发者_运维知识库]

* fine_amount.

Fine_payment has fields

* fine_payment_id[pk]

* fine_id

* payment_amount

So every Fine can have multiple payments. How would I go about selecting those Fines that haven't been paid fully and show the unpaid amount?

Oracle DB.

---

select fine_id, fine_amount
---------------------------------------------------------------
-- add a coalesce here, in case "payments" returns a NULL match
---------------------------------------------------------------
, (fine_amount - coalesce(payments.already_paid, 0)) as outstanding_amount
from Fine left outer join
(
  select fine_id
  , sum(coalesce(payment_amount, 0)) as already_paid
  from Fine_payment
  group by fine_id
) payments
on Fine.fine_id = payments.fine_id
where Fine.fine_amount <> coalesce(payments.already_paid, 0)

EDIT: inner join replaced with left outer join

---

As i am not that much perfect in using some well advanced ways in sql,what i have is the below.it might be time taking query and i strongly believe we can tune that.

SELECT a.fine_id,b.SUM_of_amount_paid,(a.fine_amount-b.SUM_of_amount_paid) amount_left_to_be_paid 
FROM 
   (SELECT fine_id,fine_amount FROM fine) a,
    (SELECT fine_id,SUM(fine_payment_amount) SUM_of_amount_paid FROM fine_payment GROUP BY fine_id ) b 
WHERE a.fine_id=b.fine_id 
AND 
(a.fine_amount-b.SUM_of_amount_paid) >0