average time? plz help

[dgoulston]

i have a field on my db, called "Sessiontime" which is a numerical field in seconds. i need to get the averages for example sessiontime<10, sessiontime>10 And <30, sessiontime>30 And <60 etc..
and find the average of them ranges over the total time eg...

SELECT Sum(AcctSessionTime) AS SumOfAcctSessionTime
FROM mytable
WHERE (((AcctSessionTime)<10));

but im having to do them all in separate querys eg. the one above is just for <10 and i would like to do them all in 1 query.

is this possible? please help.
if you need any more information before you can help i would be glad to provide it.


thankyou in advance
Darren Goulston

 

[ColinEssex]

The best way is to write a function to do this.

You can either use 'select case' or an "If" statement, I'll tell you the 'If' way.

Function Groups(x)

If x <10 then
Groups = "0 - 10"
ElseIf x <=30 then
Groups = "10 - 30"
ElseIf x <=60 then
Groups = "31 = 60"
Else
Groups = ">60"
End If

Put that in the query grid like this

Timegroups:Groups([SessionTime])

Then it'll add a group to the query then you add a count on the end.

Hope this helps

Col

 

[dgoulston]

cool

yep that worked fine.. thanx a lot.