Outputting the correct balance from payments in each row

Question

This is probably simple. But I’m at a standstill. I am using Coldfusion 2021 on a Windows PC. I am trying to Output data that has the sum of payments made by a couple of individuals. This sum of payments is subtracted from a goal number of $425. When one of our members make a payment of say $5 dollars one day, then $20 another day, the output would total their payments to date of $25. But I want that payment to date to subtract from the static number of $425 within the output which should be a balance of $400 and so on. But I’m getting some wacky results in my balance column whenever I cfoutput my query. Anyone have any ideas that may point me in the right direction? Below is the code that I used and Below that are two images, one shows the incorrect balance column and the other one shows my desired balance column.

[![Below is the code that I used and Below that are two images, one shows the incorrect balance column and the other one shows my desired balance column.]

Answer 1

Because you have a hard-coded activityCost and activityDeposit (or obtained from elsewhere), there's no need to send them to the database at all. Just get the SUM() of the payments, then in your single-row-per-group output, do the math there, like:

#activityCost - activityDeposit - expense.balance#

And expense query's "balance" column, you might rename it after removing the unnecessary values to just "payments" since that's really what you're grabbing from the database.