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

Your queries are more complicated than necessary. This will get your data:

select fname, lname, sum(buspayamount) as AmountPaid
from name join bustrip on name.foiid = bustrip.busfoiid
where whatever, maybe a date range
group by fname, lname

This will display it:

<table>
<tr> table headers go here
</tr>
<cfoutput query = "nameofquery">
<tr>
<td> #fname# #lname#</td>
<td>$#AmountPaid#</td>
<td>$425-#AmountPaid#</td>
closing tags