Average over years?

[mozingod]

OK, I have a database populated will Sales Histories and/or quotes. I'm allowing the user to search by mill name/part number/or location from that database, and it returns the relivant results.

Now, the main user I'm developing this for has asked that I include this feature... if a part number AND assembly number (both seperate fields in the database) are the same, she'd like the average of quotes per year. Here's a quick example:

PartNum InspectionNum Quote Year
------- ------------- ----- ----
90343213 ADKO3 $3503 1999
90343213 ADKO3 $2395 1999
90343213 ADKO3 $4323 1999
90343213 ADKO3 $3453 2000
90343213 ADKO3 $5645 2000

How can I do this? Right now, I'm getting, say, the part number they want to search for and throwing it into the SQL SELECT statement, then doing a good ol fashion Do While Not Rs.EOF....Loop, to write out the table of all the matches. I was thinking either some sort of variable that I could tally up then use as an average, or when I hit a date do another SQL SELECT with the year as the WHERE clause and looping through that resulting RecordSet.

Hope that wasn't too confusing. Any help or ideas would be greatly appreciated (getting kinda annoyed by this!!) Thanks!! Darrell Mozingo

 

[mwolf00]

Have you tried a query like this....

SELECT partNum, year, avg(quote) FROM myTable GROUP BY partNum, year

-----------------------------------------------------------------
Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com

 

[Tarwn]

Well, your idea of keeping a running total would work as well, you could even output it when the year changes, sectioning off the differant years of the table quite nicely:

Dim runTtl, runCnt, tYear
runTtl = 0
runCnt = 0
tYear = 0
Do Until rs.EOF
   If cInt(rs(&quot;year&quot;)) <> tYear AND runCnt > 0 Then
      Response.Write &quot;<tr><td colspan='2'>Average Quote:</td><td>&quot;&runTtl/runCnt&&quot;</td></tr>&quot;
      runTtl = 0
      runCnt = 0
      tYear = rs(&quot;year&quot;)
   End If
   runTtl = runAvg + cDbl(rs(&quot;quote&quot;))
   runCnt = runCnt + 1
   'output your normal row here
   rs.MoveNext
Loop
'output for the last year
If runCnt > 0 Then
    Response.Write &quot;<tr><td colspan='2'>Average Quote:</td><td>&quot;&runTtl/runCnt&&quot;</td></tr>&quot;
End If

Just a thought
-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html