Average post count between 2 dates?

[Geraldm]

Hi,

I need some help programming the following:

I need to get the top 10 forum members which have the highest average daliy post count between the 17th April 07 and the 18th of June 07.

If anyone can come up with some code to display this I am willing to pay $$ for your efforts

Thanks,
Gerald.

[Eikinskjaldi]

Originally Posted by Geraldm

Hi,

I need some help programming the following:

I need to get the top 10 forum members which have the highest average daliy post count between the 17th April 07 and the 18th of June 07.

If anyone can come up with some code to display this I am willing to pay $$ for your efforts

Thanks,
Gerald.

[sql]
select userid, count(*) as 'total' from post
where dateline between (unix_timestamp('2007-04-17'), unix_timestamp('2007-06-18'))
group by userid order by total desc limit 10;
[/sql]

[Geraldm]

Hi,

Thanks, that works, although I had to modify it like so:
[sql]SELECT count(*) as 'total' from post
where dateline between UNIX_TIMESTAMP('2007-04-17 00:00:00')
and UNIX_TIMESTAMP('2007-06-18 23:59:59')
group by userid order by total desc limit 10;
[/sql]

But it only outputs on column called total .... how do I also display the userid ??

Cheers ....
Gerald.

Originally Posted by Eikinskjaldi

[sql]
select userid, count(*) as 'total' from post
where dateline between (unix_timestamp('2007-04-17'), unix_timestamp('2007-06-18'))
group by userid order by total desc limit 10;
[/sql]

[Dismounted]

[sql]SELECT `userid`, COUNT(*) AS 'total'
FROM `post`
WHERE `dateline` BETWEEN UNIX_TIMESTAMP('2007-04-17 00:00:00')
AND UNIX_TIMESTAMP('2007-06-18 23:59:59')
GROUP BY `userid`
ORDER BY `total` DESC
LIMIT 10[/sql]

[Geraldm]

Doh !!!!!!
I forget to add the select userid !!!!!!!

Thanks everyone for all their help!!

Cheers ...
Gerald.