Skip to main content
R
RLByrd78240
Inspiring
January 14, 2012
Answered

Cumulative Totals...

  • January 14, 2012
  • 2 replies
  • 1624 views

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>

    This topic has been closed for replies.
    Correct answer RLByrd78240

    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>

    2 replies

    ilssac
    ilssac
    Inspiring
    January 17, 2012

    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.

    R
    RLByrd78240AuthorCorrect answer
    Inspiring
    January 17, 2012

    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
    ilssac
    Inspiring
    January 17, 2012

    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.  :-)]

    BKBK
    BKBK
    Braniac
    January 16, 2012

    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>  

    R
    RLByrd78240Author
    Inspiring
    January 16, 2012

    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! 

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices