CODEWITHSUNDEEP
mysqlsqlsubquery
Mr R
Mr R

Reputation: 95

sql query with calculation from subquery

I have two tables (well I have more but I simplify it some for this question)

Invoice

invoiceID 10

invoiceNo 1234

invoiceAmount 1000

invoiceStatus 2

Payments

paymentID 3

invoiceID 10

paymentAmount 500

paymentMethod 3

Now I need a query that gives me some values from table Invoice but also a calculation based on values from Payments for a certain invoiceID. What I would like to get is:

Invoice number, invoice amount and remaining amount to pay
--------------  ---------------    -----------------------
1234            1000               500

Can you help me finish up the query with a subquery that actually works.

select i.invoiceNo as 'Invoice Number', i.invoiceAmount as 'Invoice amount' (i.invoiceAmount - totallyPayed) as reminingToPay
from Invoice i
left join Payments p on (p.invoiceID = i.invoiceID)
where 
i.invoiceStatus = 2 
and totallyPayed = (select sum(p.PaymentAmount) from Payments where p.paymentMethod in (1,2,3))

Upvotes: 0

Views: 192

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

You could do:

SELECT i.invoiceNo AS 'Invoice Number',
       i.invoiceAmount AS 'Invoice amount',
      (i.invoiceAmount - COALESCE(p.totalPayed,0)) AS remainingToPay
FROM Invoice i
LEFT JOIN (
  SELECT invoiceID,
         SUM(paymentAmount) AS totalPayed
  FROM payments
  WHERE paymentMethod IN (1, 2, 3)
  GROUP BY invoiceId
  ) p
  ON p.invoiceID = i.invoiceID
WHERE i.invoiceStatus = 2

First you get the sum of paymentAmount from payments table for each invoiceID and then you join with your invoice table to get the remainingToPay.

Upvotes: 1

Related Questions