CODEWITHSUNDEEP
sqlsql-server
Mark
Mark

Reputation: 523

How to use column from main query in subquery?

I have a table Invoice with a column total. Then I have a table Payments with a column amount (Usually there are several payments to one invoice). I need a column balance which is the difference of Invoice.Total - (total of payments made on that invoice). This is what I have (Oh ya using Azure Sql Server)

select I.Invoice_Id, 
       I.Total - (select sum(Amount) from Payments P
                  where I.Invoice_Id = P.Invoice_Id) as Balance,
       Q.Quote_Id,
       Q.Description,
       Q.Vendor_Num
from Invoice as I
    inner join Payments as P on I.Invoice_Id = P.Invoice_Id
    inner join Quote as Q on Q.Quote_Id = I.Quote_Id;

Eventually this will be a view showing what invoices have balance owed. If I remove the where in the sub query it gives me an answer but it is the sum of all payments. I just want the sum of payments made on that invoice. Any help would be appreciated.

Thanks

Upvotes: 4

Views: 4772

Answers (2)

Steve Davis
Steve Davis

Reputation: 297

There's two approaches to this. You could either subquery or group by. If you're doing a subquery you don't need the table in the main query. Also the inner join to Payments would mean that invoices without payment would not be returned by the query. Changing it to a left outer join in the Group By example will return NULL rows when the I.Invoice_Id = P.Invoice_Id is not met.

Group By:

SELECT I.Invoice_Id, 
       I.Total - sum(ISNULL(P.Amount,0)) AS Balance,
       Q.Quote_Id,
       Q.Description,
       Q.Vendor_Num
  FROM Invoice AS I
  JOIN Quote AS Q on Q.Quote_Id = I.Quote_Id
  LEFT JOIN Payments AS P on I.Invoice_Id = P.Invoice_Id
 GROUP BY I.Invoice_Id, I.Total, Q.Quote_Id, Q.Description, Q.Vendor_Num

Subquery:

SELECT I.Invoice_Id, 
       I.Total - (SELECT ISNULL(SUM(Amount),0) FROM Payments P WHERE P.Invoice_Id = I.Invoice_Id) AS Balance,
       Q.Quote_Id,
       Q.Description,
       Q.Vendor_Num
  FROM Invoice AS I
  JOIN Quote AS Q on Q.Quote_Id = I.Quote_Id

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

I suspect your query is returning multiple results (duplicates per payment) since you are joining on the payments table.

One option would be to just remove that join to the payments table. Here's an alternative option which moves the correlated subquery to a join:

select I.Invoice_Id, 
       I.Total - p.SumAmount as Balance,
       Q.Quote_Id,
       Q.Description,
       Q.Vendor_Num
from Invoice as I
    inner join Quote as Q on Q.Quote_Id = I.Quote_Id;
    inner join (
        select invoice_id, sum(amount) SumAmount
        from Payments
        group by invoice_id) as P on I.Invoice_Id = P.Invoice_Id

Upvotes: 0

Related Questions