I have a database that has 4 tables.  One table holds the names of players 

on a baseball team.  The other three hold separate stats for these players 

(batting, pitching and fielding).  What I am trying to do is this: I first 

separate each stat table into separate recordsets.  I want to loop through 

each player in the name database and filter the batting recordset for each 

player.  I then want to find the sum of each numeric field in the 

recordset (there are about 10 in batting).  Is there a way to do this 

without having to use up precious CPU time by looping and summing each 

field (i.e. Stat1Sum = Stat1Sum + rs(Stat1)).  I also don't want to keep 

doing a Sum SQL statement for each player since this could use up valuable 

server resources.  If anyone out there knows of a way to do this, please 

let me know.  Anything at this point would be a great help.



Chris

Ah you want to do something, but don't want to use any server resources to

do it. If you can find out how to this please let me know.



You have to use some resources to do what you want. SUM is going to use a

lot less resources than Stat1Sum = Stat1Sum + rs(Stat1). Use SUM, it is

almost certain to be your best option.



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: chrscote@9... [mailto:chrscote@9...]

Sent: Friday, 5 October 2001 11:28 AM

To: ASP Databases

Subject: [asp_databases] Need help with totaling fields for separate

people





I have a database that has 4 tables.  One table holds the names of players 

on a baseball team.  The other three hold separate stats for these players 

(batting, pitching and fielding).  What I am trying to do is this: I first 

separate each stat table into separate recordsets.  I want to loop through 

each player in the name database and filter the batting recordset for each 

player.  I then want to find the sum of each numeric field in the 

recordset (there are about 10 in batting).  Is there a way to do this 

without having to use up precious CPU time by looping and summing each 

field (i.e. Stat1Sum = Stat1Sum + rs(Stat1)).  I also don't want to keep 

doing a Sum SQL statement for each player since this could use up valuable 

server resources.  If anyone out there knows of a way to do this, please 

let me know.  Anything at this point would be a great help.



Chris

 

Why not hold the sum in a separate table, and have a trigger to update it

when the other stats change ?







-----Original Message-----

From: David Cameron [mailto:dcameron@i...] 

Sent: Friday, 5 October 2001 3:18 PM

To: ASP Databases

Subject: [asp_databases] RE: Need help with totaling fields for separate p

eople







Ah you want to do something, but don't want to use any server resources to

do it. If you can find out how to this please let me know.



You have to use some resources to do what you want. SUM is going to use a

lot less resources than Stat1Sum = Stat1Sum + rs(Stat1). Use SUM, it is

almost certain to be your best option.



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: chrscote@9... [mailto:chrscote@9...]

Sent: Friday, 5 October 2001 11:28 AM

To: ASP Databases

Subject: [asp_databases] Need help with totaling fields for separate people





I have a database that has 4 tables.  One table holds the names of players 

on a baseball team.  The other three hold separate stats for these players 

(batting, pitching and fielding).  What I am trying to do is this: I first 

separate each stat table into separate recordsets.  I want to loop through 

each player in the name database and filter the batting recordset for each 

player.  I then want to find the sum of each numeric field in the 

recordset (there are about 10 in batting).  Is there a way to do this 

without having to use up precious CPU time by looping and summing each 

field (i.e. Stat1Sum = Stat1Sum + rs(Stat1)).  I also don't want to keep 

doing a Sum SQL statement for each player since this could use up valuable 

server resources.  If anyone out there knows of a way to do this, please 

let me know.  Anything at this point would be a great help.



Chris

 



 

Depending on your database and the number of times the page is hit this

might not offer a speed gain over SUM(). If you are using Access you don't

have access to triggers.



What you are suggesting is a bad solution from a database normalisation

point of view. You are storing data more than once. Once you start down this

track you might as well store values for every aggregate that you ever use

(COUNT() , AVG(), MIN() MAX() etc..)



I still think that SUM() is your best option.



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: Aaron Bull [mailto:aaronb@a...]

Sent: Friday, 5 October 2001 3:50 PM

To: ASP Databases

Subject: [asp_databases] RE: Need help with totaling fields for separa

te p eople







Why not hold the sum in a separate table, and have a trigger to update it

when the other stats change ?

OK, here's another suggestion I got from work.  Please tell me if you 

think this will work...

Either use an Inner Join query from my stat table and player table with a 

sum of each field in a single query.  Here is how it was explained to me.

First, I have a recordset gather all of the data in one stats table and 

one for the player names.  Then, since you have those 2 recordsets, you 

can disconnect from the database.  Next, create a SQL INNER JOIN string 

consisting of a Sum for each field in the stat table inner joined with the 

player table.  Since I have the stats in a table, it would be easy to make 

the query by using the rs.fields(n).name.  

    (i.e. SQL=SQL & "SUM("rs.fields(n).Name & " AS Sum" & n & "INNER JOIN 

players.Name;".



Then create another recordset to hold the result of this query. According 

to the person who told me this method, what you end up with is a recordset 

of just the sums for each Players.Name in each field.



If this sounds like a reasonable thing to do, please let me know.  If you 

think this is too complicated or would strain the system, please help me 

to find another way.  I do know, though, that I have at most 20 fields in 

the tables.



Chris





>Depending on your database and the number of times the page is hit this

> might not offer a speed gain over SUM(). If you are using Access you 

don't

> have access to triggers.

> 

> What you are suggesting is a bad solution from a database normalisation

> point of view. You are storing data more than once. Once you start down 

this

> track you might as well store values for every aggregate that you ever 

use

> (COUNT() , AVG(), MIN() MAX() etc..)

> 

> I still think that SUM() is your best option.

> 

> regards

> David Cameron

> nOw.b2b

> dcameron@i...