Average count over last n seconds

[sphinx9999]

Hi,

 

I'm creating a query that will calculate the average count over the last n (60) seconds. I have got the first part:

 

select count(*) as counter from logquick group by logtime order by logtime desc limit 60;

 

This brings back something like this:

 

counter
8
4
15
6
5
2
2
10
...
7
2
4
6
6
6
2
2

 

Any ideas how I can now add in the averaging segment? Currently I am doing this in the PHP but obviously doing it all in one query would be better.

 

select avg(count(*))as counter from logquick group by logtime order by logtime desc limit 60;

1111 - Invalid use of group function

 

Thanx

[fnairb]

This SQL works in Oracle. (I dont' have mysql handy)

 

select avg(counter) from ( 
   select count(*) as counter from logquick group by logtime order by logtime desc limit 60
);

[sphinx9999]

That looked gr8 but unfortunately gives 1248 - Every derived table must have its own alias. I assume this must mean that the outer select must be named...

 

Update: Just added 'as t' and its working fine now! Full query is

select avg(counter) from (
  select count(*) as counter from logquick group by logtime order by logtime desc limit 60
) as t;

 

Multi-thanx for your help fnairb