Depending on your database you can use something like this.. select sum (cost) over () as total_sum, sum(cost) over (partition by acct) as total_acct, name, cost from travel where reconciled = 'No'
As stated this may not work for you.. depends on your DB. On Mon, Dec 22, 2008 at 9:39 AM, <[email protected]> wrote: > 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:4248 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
