asked on

Adding column to itself from a query

Experts-

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?
Thanks
Nick

<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
</cfquery>

_agx_

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

_agx_

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.

nmarano

ASKER

thanks I'll try it out...if not I can try to run it in SQL with the QoQ

_agx_

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.

ASKER CERTIFIED SOLUTION

Rodrigo Munera

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Julian Hansen

You can try this

SELECT 
    d.s_spendDate, 
    spend, 
    @running_sum:=@running_sum + d.spend AS Total
FROM ( 
    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

_agx_

@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 Munera

Huh! Learned something new :) thx _agx_!

_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.

nmarano

ASKER

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

nick

Rodrigo Munera

Yeah, I've made that mistake a few times myself :) glad to help!

_agx_

Yep, haven't we all :) Glad everything worked out!