[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