Try this:
<cfquery name="GetCostSum" datasource="travel">
SELECT SUM(isNull(Cost,0) AS CostSum, Acct, Name, Cost
from travel
grouping Name
WHERE reconciled = 'No'
GROUP BY Acct, Name, Cost
WITH ROLLUP
</cfquery>
Zelda
-----Original Message-----
From: James Fuller [mailto:[email protected]]
Sent: Friday, December 19, 2008 7:15 PM
To: cf-newbie
Subject: Re: need to get SUM subtotal for a group query, and total SUM
> 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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4247
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
