help! need to accumulate total
 

:rolleyes: I am trying to develope a simple application that will record a teams miles per week and total miles I have the code working to accumulate the total, but the problem is how to I get it to accumulate just for the one team and not for all? How do I get it to accumulate for just one team Name? Code is as follows:

Code:
<CFQUERY NAME="Mileage" DATASOURCE="#dsn#">SELECT weeklyMileage, totalMileageFROM mileage</CFQUERY>cfset totalMileage= 0> <cfloop query="Mileage"><cfset totalMileage =totalMileage + Mileage.weeklyMileage></cfloop> <cfquery datasource="#dsn#" dbtype="ODBC"> Insert Into mileage (dateupdated,teamName,teamLeader,email,weeklyMileage,mileageWeekNumber,comments,totalMileage) Values ('#Trim(thetime)#','#Trim(teamName)#','#Trim(teamLeader)#','#Trim(email)#','#Trim(weeklyMileage)#','#Trim(mileageWeekNumber)#','#Trim(comments)#','#Trim(totalMileage)#')</cfquery>

Re: help! need to accumulate total
 

Missing a where?

SELECT weeklyMileage, totalMileageFROM mileage WHERE teamName=#teamName#

Re: help! need to accumulate total
 

You also may want to look into using the GROUP by clause. For example, if your data is as follows:

team, week_nbr, week_miles
A,1,4
A,2,4
A,3,6
B,2,5
C,1,3
C,2,6

You could write a query like:

SELECT team, SUM(week_miles) as total_miles
FROM your_tbl_name
GROUP BY team

Which would return
team, total_miles
A, 14
B, 5
C, 9


Like nikkiH said, you can also use this with a where clause.

Good luck,
Dan