Cumulative Totals...

[RLByrd78240]

I'm not quite the seasoned pro, but have been working with CF for about 5 years and usually can get it to do what I want.  I thought this would be relatively easy (and prob is to you) to add cumulative costs in the right column of a balance sheet type output, but it's kicked my butt long enough..lol

Just need it to sum cumulative costs of the items selected, in the order sorted. I've tried varying ways to get this to add and display like I need, dbqueries, loop indexes, using recordcount/currentrow calculations, etc, etc...no joy

Appreciate your time...really! 

<form...select...etc>

<cfquery name="getcosts" datasource="xxx">

SELECT *

FROM costs

ORDER by '#form.sortby#'

</cfquery>

<cfoutput query="getcosts">

*displays columns for #item_name#, #item_cost#, and cumulative cost* based on output sort order

</cfquery>

[BKBK]

RLByrd78240 wrote:

<cfquery name="getcosts" datasource="xxx">

SELECT *

FROM costs

ORDER by '#form.sortby#'

</cfquery>

<cfoutput query="getcosts">

*displays columns for #item_name#, #item_cost#, and cumulative cost* based on output sort order

</cfquery>

I have some doubts about the clause ORDER by '#form.sortby#'. Does it actually work? should it at least be ORDER by #form.sortby# (without the quotes)? Test by dumping getcosts.

When grouping a set of records, you should use the order-by column of the query as the group attribute of the cfoutput tag. In other words, something like

<cfoutput query="getcosts" group="#form.sortby#">

To get cumulative costs, you could use a construction like

<cfset cumulative = 0>

<cfoutput>

blah blah blah

<cfset cumulative = cumulative + item_cost>

</cfoutput>  

[RLByrd78240]

Sorry, I had 20 other queries in my head, didn't mean to put the table column name in tick marks...my 'real' code doesn't have them

I'll have to try your suggestion tmw when I get to work.

Thanks for your response BK, I was beginning to wonder if my ? was too difficult! 

[RLByrd78240]

No joy

It's still not 'summing' all of the previous rows, based on (less than) the currentrow (regardless of the sortby).

Surely there are folks who've done financial statements (aka, balance sheets) similar to this?

[ilssac]

You might need to provide more details about what you are trying to do and what difficulty you are having accomplishing it.

From your description it sounds like to me that you just need to display a running total?

I.E.

<cfset runningTotal = 0>

<cfoutput query="getcosts">

<cfset runningTotal = runningTotal + item_cost>

#item_name#, #item_cost#, #runningTotal#

</cfquery>

If that is not what you are attempting to do, then please provide more details.

[RLByrd78240 · Accepted solution]

Thanks IE, that put me close enough to the ballpark I was able to solve.  In order to get cumulative sum, just had to cfloop what you put above.  Think I was just trying to make it too difficult!

<cfset runningTotal = 0>

<cfloop query="getcosts">

<cfset runningTotal = runningTotal + item_cost>

#item_name#, #item_cost#, #runningTotal# <br>

</cfloop>

[ilssac]

Your welcome.

Quick gratis language lesson.  I.E. is not my handle or initials.  It is short for the latin phrase "id est" which means "That is".

http://public.wsu.edu/~brians/errors/e.g.html

Of course reading through that grammer note, I probaby shoud have used "e.g." short for "exempli gratia" i.e. "For Example"* which is closer to what I meant to imply.

[*See what I did there.  :-)]