I'm using an SQL Server database which has
which also includes
select sum(r.amount_paid), o.invoice_id from RECEIPT r, INVOICE i, ORDER o
where r.invoice_id = i.invoice_id
and o.invoice_id = i.invoice_id
group by o.invoice_id;
Here's three answers to three slightly different questions you might ask of your database.
If you are trying to get the total amount paid per invoice, all you need is
SELECT SUM(Amount_Paid) Total_Paid, Invoice_ID FROM Receipt GROUP BY Invoice_ID
If you want to know the total paid per order, this is not quite possible in your data model as you have described it. If a invoice has three orders on it, and the invoice is only partly paid, there is no way to tell which of the the orders is paid and which is not.
You need some additional data structure that indicates how payments are applied to orders within an invoice, e.g. an Allocation or Split table.
On the other hand, if you want to know how much payment has been received on invoices that contain one or more order IDs, you could write this:
SELECT SUM(Amount_Paid) Total_Paid, Invoice_ID FROM Receipt WHERE Invoice_ID IN (SELECT Invoice_ID FROM Order WHERE Order_ID IN (1,2,3,4)) --Edit these IDs for your specific case GROUP BY Invoice_ID
Notice none of the queries above required any joins, so no multiplying :)