0

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.

3
  • You can just change p.payment_amount to SUM(p.payment_amount) Commented Feb 26, 2019 at 3:59
  • Perfect! It’s funny I was just thinking “I bet there has to be a sum() option...I mean, everything has a sum() option....haha Commented Feb 26, 2019 at 4:00
  • Make that an answer and I’ll accept it Commented Feb 26, 2019 at 4:01

2 Answers 2

1

You can just change p.payment_amount to SUM(p.payment_amount) in your subquery. Note that you have an error in the subquery, it should probably be

(SELECT SUM(p.payment_amount) FROM Payments as p WHERE p.order_id = o.id)

Note change from o.order_id to p.order_id.

Sign up to request clarification or add additional context in comments.

3 Comments

Sorry that was a typo. I changed the actual query on here to make more sense because the one I have has a lot more irrelevent information being pulled. Also, If I’ve got p.payment_aount and another column o.amount_paid. Is there a way to add those two together in the query as a separate value?
I presume o.amount_paid is in the orders table, and there is only one value per order? If so, you could write amount_paid + (SELECT SUM(p.payment_amount) FROM Payments as p WHERE p.order_id = o.id) AS total_paid_amount or something similar
Would it be (o.amount_paid + sum(p.payment_amount)) as total_paid_amount
0

Try this query :

SELECT o.id, o.total, SUM(p.payment_amount) FROM Orders o, Payments p where p.order_id = o.id

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.