Subtract values from two columns in sql query

Question

There are two columns and i want to subtract them. I am trying to sum [payment] column then subtracting it from [total_bill] column. Below but is my code and it gives me error that it Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SELECT SUM((bill_record.total_bill)-SUM(invoice_payments.payment)) AS [LEFT AMOUNT] 

Answer 1

You need to use group by but also you probably just want to sum the payments and subtract those against the single total bill.

SELECT bill_record.total_bill - SUM(invoice_payments.payment) AS [LEFT AMOUNT] 
FROM bill_record INNER JOIN invoice_payments ON bill_record.PKColumn = invoice_payments.FKColumn 
WHERE bill_record.PKColumn = @billId
GROUP BY bill_record.total_bill

---

Note that the group by here works because you are also filtering in the WHERE clause. If you want to get the results for multiple bills you would also group by a unique bill identifier and have it be returned in the SELECT.

SELECT bill_record.PKColumn AS BillId, bill_record.total_bill - SUM(invoice_payments.payment) AS [LEFT AMOUNT] 
FROM bill_record INNER JOIN invoice_payments ON bill_record.PKColumn = invoice_payments.FKColumn 
GROUP BY bill_record.PKColumn, bill_record.total_bill