Performing CF Math Functions

[Guest_imported]

If I'm pulling in a query with a list of stats and due to certain restrictions on the way I'm pulling the data, I can't SUM it in my database, how could I go about adding them together in CF.

For example:

<cfquery name=&quot;stats&quot;>
SELECT player,
week,
team,
stat1,
stat2

FROM Stats

WHERE Team = url.team
</cfquery>

Now because stats are listed in week format I want to be able to add all the stat1 and stat2 columns where team = this team. Players may be assigned to different teams depending on the week. I tried to create a view or do this in SQL and I couldn't figure it out.. Any help would be appreciated.

Something like...

<cfoutput query=stats>
#stats.stat1.row1 + stats.stat1.row2#
</cfoutput>

I know that won't work but I think you see what I'm getting at...

 

[tlhawkins]

Hey FL,

You're real close with your Output Query.

Try this:

<cfset SumofStat=0>
<cfoutput Query=&quot;stats&quot;>
<cfset SumofStat = SumofStat + #stats.stat1#>
</cfoutput>
#SumofStat#

the cfoutput will loop through the rows so you don't have to bother with that. I'm not sure if the &quot;#&quot; in there are actually required but it will work like that. It will also work if you did:

<cfset SumofStat = #SumofStat# + #stats.stat1#>

just don't put the &quot;#&quot; around the first &quot;SumofStat&quot; it'll break.

have fun...

 

[Guest_imported]

Thanks, that works well however I also found a view that works. Which should I use? I'm currently using an Office 2000 Access Database. Will it be faster having the database producing the view, or by doing it in CF?

 

[tlhawkins]

In my experience having the Database do it is always faster. I was wondering why the DB wouldn't do it, I'm glad you got it to.

have fun...