Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Adding column to itself from a query

Posted on 2014-02-21
12
Medium Priority
?
229 Views
Last Modified: 2014-02-22
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>
0
Comment
Question by:nmarano
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 39877673
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

0
 
LVL 52

Expert Comment

by:_agx_
ID: 39877755
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.
0
 
LVL 2

Author Comment

by:nmarano
ID: 39877885
thanks I'll try it out...if not I can try to run it in SQL with the QoQ
0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
LVL 52

Expert Comment

by:_agx_
ID: 39877940
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.
0
 
LVL 4

Accepted Solution

by:
Rodrigo Munera earned 2000 total points
ID: 39878023
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>
  #dollarFormat(runningTotal)#
</cfoutput>

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)>
</cfloop>

Open in new window

0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 39878040
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

0
 
LVL 52

Expert Comment

by:_agx_
ID: 39878084
@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 :)
0
 
LVL 4

Expert Comment

by:Rodrigo Munera
ID: 39878086
Huh! Learned something new :) thx _agx_!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39878096
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.
0
 
LVL 2

Author Closing Comment

by:nmarano
ID: 39879255
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
0
 
LVL 4

Expert Comment

by:Rodrigo Munera
ID: 39879358
Yeah, I've made that mistake a few times myself :) glad to help!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39879461
Yep, haven't we all :) Glad everything worked out!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question