Question

Locked

Get subtotals in a Coldfusion group query

By mtownsend ·
Hello. Say I have a table with the following data:

Acct#, Name, Cost
001, Bennett, 50.00
001, Bradley, 250.00
001, Green, 200.00
420, Smith, 50.00
420, Cummings, 50.00
470, Brown, 80.00

I would like the output to be like:

001-6405
Bennett 50.00
Bradley 250.00
Green 200.00
Total 001 500.00

420-6405
Smith 50.00
Cummings 50.00
Total 420 100.00

470-6405
Brown 80.00
Total 470 80.00

Total Accts 680.00

I used a query like this

<cfquery name="GetCostSum" datasource="travel">
SELECT SUM(Cost) AS CostSum, Acct, Name, Cost from travel WHERE reconciled = 'No' GROUP BY Acct, Name, Cost
</cfquery>

to try and get the groups summed up together, but my sum total is only the first record so it looks like the following. The output is:

<cfoutput query="GetCostSum" group="Acct">
<strong>#Acct#-6405</strong><br />
<cfoutput>
#Name# #DollarFormat(Cost)#<br />
</cfoutput>
Total: #DollarFormat(CostSum)#<br /><br />
</cfoutput>

001-6405
Bennett 50.00
Bradley 250.00
Green 200.00
Total 001 50.00

420-6405
Smith 50.00
Cummings 50.00
Total 420 50.00

470-6405
Brown 80.00
Total 470 80.00

Do I need to do some kind of a loop to get the subtotals for each account? Thanks.

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Share your knowledge
Back to Web Development Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums