I have an online sales system I am developing and I’m working on the billing payment system.
I have the order total $ amount recorded on the database table with the order itself.
Example:
SELECT total FROM Orders WHERE id = '1'
Then, I’ve got another table that includes an individual record for each financial transaction (check, cc, etc.)
Example:
SELECT payment_amount FROM Payments WHERE order_id = '1'
What I would like to do is combine these two together when doing some reporting of which orders have not been paid in full and retrieve the balance of each order. I’d like to do this with a single query if possible...
This was what I tried...
SELECT o.id as order_id, o.total, (SELECT p.payment_amount FROM Payments as p WHERE o.order_id = o.id) as amount_paid_plus FROM Orders as o
This works great if there is only 1 entry in payments...but if there are two, I get this error
Subquery returns more than 1 row
I want the payments table results to be added together into one lump sum amount and returned as one variable in the query
I also need the query to still return info even if there are no payments in the payments table. It will just display amount paid as 0.
p.payment_amounttoSUM(p.payment_amount)