sphinx9999 Posted March 7, 2008 Share Posted March 7, 2008 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 Link to comment https://forums.phpfreaks.com/topic/94878-average-count-over-last-n-seconds/ Share on other sites More sharing options...
fnairb Posted March 7, 2008 Share Posted March 7, 2008 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 ); Link to comment https://forums.phpfreaks.com/topic/94878-average-count-over-last-n-seconds/#findComment-486053 Share on other sites More sharing options...
sphinx9999 Posted March 7, 2008 Author Share Posted March 7, 2008 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 Link to comment https://forums.phpfreaks.com/topic/94878-average-count-over-last-n-seconds/#findComment-486068 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.