[thelist] help with SQL query
Steve Lewis
slewis at macrovista.net
Wed Jul 31 17:11:01 CDT 2002
Michael Robertson wrote:
> This seems to return what I want on a week by week basis
>
> SELECT avg(steps) FROM tblRecordSteps where UserID='777' GROUP BY
> ProgramWeek
Lets be very specific here please.
do you want how many steps a user takes in a seven day period through
that user's history in the program?
SELECT (avg(steps) * 7) from RecordSteps where UserID='777'
or do you want a true average number of steps taken in a program-week
over the scope of all program-weeks.
SELECT avg(weeksteps) from (SELECT sum(steps) as weeksteps, programweek
FROM RecordSteps where UserID='777' GROUP BY programweek) s
[plz excuse, I am shooting from the hip on these queries and there may
be silly syntax issues]
With statistical work, I find it very useful to try to describe/reduce
the data using algebraic methods first. This reduces the imprecision of
language in defining the problem.
> Can i get an overall average from this - I tried using this as a subquery
> and got an error
It probably has something to do with how you used it as a subquery.
Derived tables require different syntax then a true Subquery. What I
have done above is a derived table (referenced in a FROM rather than in
the SELECT list), and derived tables must be given a table alias (I used
the alias of s above).
More information about the thelist
mailing list