How do I get a subtotal of a group in a Coldfusion query?

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.
jomark28Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

duncancummingCommented:

<cfoutput query="GetCostSum" group="Acct">
	<strong>#Acct#-6405</strong><br />
	<cfset total = 0>	<!--- reset for each new Acct --->
	<cfoutput>
		#Name# #DollarFormat(Cost)#<br />
		<cfset total = total + Cost>
	</cfoutput>
	Total: #DollarFormat(total)#<br /><br />
</cfoutput>

Open in new window

0
black0psCommented:
There might be an easier way to do it with SQL, but i don't know what it is. I would just use a running tally.

<cfset runningtotal = 0>
<cfset grandtotal = 0>
<cfoutput query="GetCostSum" group="Acct">
<strong>#Acct#-6405</strong><br />
<cfoutput>
<cfset runningtotal = runningtotal + Cost>
#Name# #LSCurrencyFormat(Cost)#<br />
</cfoutput>
<cfset grandtotal = grandtotal + runningtotal>
Total: #LSCurrencyFormat(runningtotal)#<br /><br />
</cfoutput>
Grand Total: <cfoutput>#LSCurrencyFormat(grandtotal)#</cfoutput>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
duncancummingCommented:
also make sure you use ORDER BY Acct in your SQL.  
0
jomark28Author Commented:
Thanks a lot! I just had to remember to reset the runningtotal to 0 after each Acct#.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.