[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