[thelist] help with SQL query
rudy
r937 at interlog.com
Wed Jul 31 10:22:07 CDT 2002
> Working with an unknown number of weeks is it possible
> to get the average for each week
select UserID
, ProgramWeek
, avg(Steps) as weeklyavg
from yourtable
group
by UserID
, ProgramWeek
> then get the avg of all weeks combined in a select statement?
yes, this one's a bit trickier, because you take the average of the
averages above, but thanks to standard sql (which i believe sql/server
supports), you can just do
select UserID
, avg(weeklyavg)
from ( select UserID
, ProgramWeek
, avg(Steps) as weeklyavg
from yourtable
group
by UserID
, ProgramWeek )
group
by UserID
in the other note you posted, you said
> I'm tryng to loop through an array doing a sql statement on each item
this is almost always a bad idea -- it's inefficient and doesn't scale
you can usually get what you want just by asking the database for it
mind you, the sql gets dodgy sometimes, but it can usually be wrestled into
submission, like the above average of averages example
rudy
More information about the thelist
mailing list