CODEWITHSUNDEEP
sqlsql-server
yaseen enterprises
yaseen enterprises

Reputation: 151

Subtract values from two columns in sql query

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]

Upvotes: 9

Views: 123972

Answers (5)

wassim bezine
wassim bezine

Reputation: 108

in your request you select items from what .... the request should be like 

SELECT SUM((bill_record.total_bill)-SUM(invoice_payments.payment)) AS [LEFT AMOUNT] FROM YOUR TABLE NAME

Upvotes: 0

Daniel Inbaraj
Daniel Inbaraj

Reputation: 908

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

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

As you are using SUM aggregate function so you have to use group by clause properly

and number of selection column must on group by clause

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

the of of error can be in group by clause number of column other wise your query should work

Upvotes: 0

Igor
Igor

Reputation: 62213

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

Upvotes: 7

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You seem want one sum instead of two :

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

Upvotes: 2

Related Questions