Average age in statistics
 

I would like to include an average age in statistics.
I want to make it as a mod and release it here (i love seeing people liking my "hacks" and installing them!!) but I have a very little php knowledge and none for SQL. I can learn easily though!
I just want somebody who knows to point me to the right direction and give me some guidelines, especially on the SQL part.
I read about a SQL function AVG, will it help?
Can I make this as a plugin?
I have never made a code mod before, only template mods, but I really like giving to the community. I just need some help with this as it looks quite difficult to me...
I took a look at my database and as I expected there is no "age" field, just birthday. This means my script should calculate the age itself or vb provides a way to do so?
Any help is really appreciated!! :)

Thanks in advance!

this is from my site www.hardwareforums.com

import the attached plugin (enable it is well) and in your forumhome template search for

and add this after it

Average Age: $avg_age

I'm sure it can be done without any template edits, so if someone wants to, feel free to turn it into that...

Thanks so much! Why don't you release this as a plugin? It's a nice idea I think and it would get many installs! :)

PS: I guess now I must find something else to make as my first php-hack :p Maybe a male to female ratio I guess :p

It produced a database error cause it tried to SELECT from a table that didn't exist. For some reason it treated the database name as the prefix as well.
I'll look into the code and try to see if I can fix it although I consider it quite unlikely :p

I'd use another query:
[sql]
SELECT ROUND(AVG(YEAR(NOW())-YEAR(birthday_search) - (MONTH(NOW()) < MONTH(birthday_search)) - (MONTH(NOW()) = MONTH(birthday_search) AND DAY(NOW()) < DAY(birthday_search)))) AS avgage FROM user WHERE YEAR(birthday_search)>0
[/sql]
as your Version only looks for the year, which produces incorrect results.

thanks KirbyDE I'll update it now

edit:I get this msg

1. Both of your queries don't consider the prefix I think (correct me if I'm mistaken, I don't know SQL)

2. I would prefer the average to have 1-2 decimal digits, not as an integer like it does now

(I hope my english makes sense :p)

@Sniper
This doesn't seem to be a complete mySQL erorr message

@Loukrhtia
I just posted the query, no PHP Code. And pure queries can't contain the TABLE_PREFIX constant :)
But you're right, in PHP one must add it.

If you want decimals, leave out the ROUND() and run the result through vb_number_format(), with decimals set to 2.

I made the query like that so that it has the prefix:
[sql]
SELECT ROUND(AVG(YEAR(NOW())-YEAR(birthday_search) - (MONTH(NOW()) < MONTH(birthday_search)) - (MONTH(NOW()) = MONTH(birthday_search) AND DAY(NOW()) < DAY(birthday_search)))) AS avgage FROM " . TABLE_PREFIX . " user WHERE YEAR(birthday_search)>0
[/sql]

but I get another database error now:

at least now the prefix is ok :surprised:

as strange as it might sound thats whole error message I get, I just tested it a couple of times.

Looks like we are getting the same error message Sniper...
KirbyDE, any ideas??