Adding column to itself from a query


I have this query below and would like to be able to sum the spend each day to the previous day's spend
     Spend Date   Spend Amount  total
        feb 12             20.00                 20.00
        feb 13            10.00                   30.00
       Feb  14           50.00                   80.00

Any suggestions on how I can output this?

 <cfquery name="getTotals" dbtype="query">
               select a_referenceID,sum(spend) as spend ,s_spendDate, sum(leads) as leads
            from gettheinsert
            group by a_referenceID,s_spendDate
            order by s_spendDate
Who is Participating?
Rodrigo MuneraConnect With a Mentor Sr. Software EngineerCommented:
It sounds like you want an incremental total in your totals column, I think _agx_'s code will give you the whole total for the entire query date range.
If you want to run an incremental total, you could do it as you output it, or you could loop through the query object and update the cell as you go through it.

If you're just want to update the total incrementally in the output, just set a variable that gets updated through each iteration

<cfset runningTotal = 0>
<cfoutput query="myQuery">
  <cfset runningTotal = runningTotal+spendAmount>

Open in new window

Or if you want to update your query Object you can loop through the query and update it:

<cfset queryAddColumn(myQuery,"runningTotal","double")>
<cfset myRunningTotal = 0>
<cfloop query="myQuery">
    <cfset myRunningTotal = myRunningTotal + spendAmount>
    <cfset querySetCell(myQuery,"runningTotal",myRunningTotal,currentRow)>

Open in new window

I don't know if SQL Server 2008+ have better methods, but old style would be use a subquery. Obviously use cfqueryparam for the date variables

SELECT p.spendDate
		, p.spendAmount
		, ( SELECT SUM(ISNULL(s.spendAmount, 0))
                     FROM   prospects s
                     WHERE s.spendDate >= #someStartDate#
                     AND   s.spendDate <= p.spendDate
	      ) AS Total
FROM  prospects p
WHERE p.spendDate >= #someStartDate#

Open in new window

Sorry didn't notice the dbtype="query".  Not sure if you can do that in a QoQ, but give it a shot. Just remove the ISNULL(...) which isn't supported in QoQ.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

nmaranoAuthor Commented:
thanks I'll try it out...if not I can try to run it in SQL with the QoQ
Ok. Also, I'm assuming "spendDate" stores a date only (not date and time). If it's the latter, you'll definitely need to do it in SQL so you can truncate the time and group by date. QoQ's don't support that.
Julian HansenCommented:
You can try this
    @running_sum:=@running_sum + d.spend AS Total
    SELECT s_spendDate, SUM(spend) AS spend 
    FROM gettheinsert 
    GROUP BY s_spendDate) d
JOIN (SELECT @running_sum:=0 AS dummy) dummy

Open in new window

@munerr - Nope, this clause in the subquery:

         s.spendDate <= p.spendDate

ensures it generates a running total, like in the example. However, you're right that it could be done just as easily in an output loop :)
Rodrigo MuneraSr. Software EngineerCommented:
Huh! Learned something new :) thx _agx_!
Welcome :) I should also add that if he only needs to output the totals, your 1st example is the better option. Then there's no need to modify the base query.
nmaranoAuthor Commented:
All-  Thanks for your comments!  I do need to output it and in looking at munerr example, I now see the error I made.  I had my var runningtotal = 0 inside the cfoutput which kept setting it to 0 and not adding anything.  

Thanks again

Rodrigo MuneraSr. Software EngineerCommented:
Yeah, I've made that mistake a few times myself :) glad to help!
Yep, haven't we all :) Glad everything worked out!
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.

All Courses

From novice to tech pro — start learning today.