Query question - PLEASE HELP!!!

[Lea Verou]

I really need a hack that displays the average age. Currently I am using this plugin:

$age_result=$db->query_first("SELECT ROUND(YEAR(NOW())-AVG(YEAR(birthday_search)),0) AS avgage FROM " . TABLE_PREFIX . "user WHERE YEAR(birthday_search) <> 0;");
$avg_age=$age_result['avgage'];

but as you can see, it's not accurate as it only counts the year of the birthday and the current year...

Andreas/KirbyDE had given me this query instead that is supposed to be more accurate:


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


but it gives a database error:
Database error in vBulletin 3.5.0:

Invalid 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 testvb_user WHERE YEAR(birthday_search)>0;

MySQL Error : You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(NOW()) < DAY(birthday_search)))) AS avgage FROM testvb_user WH
Error Number : 1064

Any ideas please? :nervous:

[Xenon]

try that query


SELECT ROUND(AVG(YEAR(NOW()) - YEAR(birthday_search) - (DAYOFYEAR(birthday_search) - DAYOFYEAR(NOW())) / 365 AS avgage
FROM testvb_user
WHERE YEAR(birthday_search)>0;

[Lea Verou]

Thanks so much but I still get a db error... :(

Database error in vBulletin 3.5.0:

Invalid SQL:
SELECT ROUND(AVG(YEAR(NOW()) - YEAR(birthday_search) - (DAYOFYEAR(birthday_search) - DAYOFYEAR(NOW())) / 365 AS avgage
FROM testvb_user
WHERE YEAR(birthday_search)>0;;

MySQL Error : You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS avgage
FROM testvb_user
WHERE YEAR(birthday_search)>0' a
Error Number : 1064

This is how I put it in the plugin:
$age_result=$db->query_first("SELECT ROUND(AVG(YEAR(NOW()) - YEAR(birthday_search) - (DAYOFYEAR(birthday_search) - DAYOFYEAR(NOW())) / 365 AS avgage
FROM testvb_user
WHERE YEAR(birthday_search)>0;");
$avg_age=$age_result['avgage'];

[Marco van Herwaarden]

Add a ')' in front of the AS avgage.

[Lea Verou]

Database error in vBulletin 3.5.0:

Invalid SQL:
SELECT ROUND(AVG(YEAR(NOW()) - YEAR(birthday_search) - (DAYOFYEAR(birthday_search) - DAYOFYEAR(NOW())) / 365) AS avgage
FROM testvb_user
WHERE YEAR(birthday_search)>0;;

MySQL Error : You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS avgage
FROM testvb_user
WHERE YEAR(birthday_search)>0' a
Error Number : 1064

:( :( :(
I have started to think that it will never work out... :( :( :(

[Xenon]

it's just a problem with brackets ;)

SELECT ROUND(AVG(YEAR(NOW()) - YEAR(birthday_search) - (DAYOFYEAR(birthday_search) - DAYOFYEAR(NOW()) / 365))) AS avgage
FROM testvb_user
WHERE YEAR(birthday_search)>0;

[Lea Verou]

Now there is no database error (woo hoo!! :D) but...
Average Age: -150 :(

[Xenon]

very young users you have ;)

my fault, try that:

SELECT ROUND(AVG(YEAR(NOW()) - YEAR(birthday_search) - (DAYOFYEAR(birthday_search) - DAYOFYEAR(NOW())) / 365)) AS avgage
FROM testvb_user
WHERE YEAR(birthday_search)>0;

[Lea Verou]

It looks correct, thanks so much!!!!!! :D :D :D

Another question, not so urgent, answer if and when you have time... How can I make it to display 2 decimals? :)

Thanks again!

Also, do you allow me to release this as a modification? Others may need it too. Of course, I'll include the appropriate credits :)

[Xenon]

glad we could solve it :)

2 decimals are very easy:

SELECT ROUND(AVG(YEAR(NOW()) - YEAR(birthday_search) - (DAYOFYEAR(birthday_search) - DAYOFYEAR(NOW())) / 365), 2) AS avgage
FROM testvb_user
WHERE YEAR(birthday_search)>0;

feel free to release it

[Lea Verou]

Thanks a lot!!!!
I'll release it but it will be quite messy as the user will have to manually change the plugin to enter his db prefix, if I try to put the variable TABLE_PREFIX it gives a db error for no reason. Really weird! :)

[Marco van Herwaarden]

What is the exact php code you are using to run this query?

[Lea Verou]

What is the exact php code you are using to run this query?

Look at the first message, I am using the same code with a different query ;)

[Marco van Herwaarden]

That should not give a SQL error on the TABLE_PREFIX. What is the exact error?

[Lea Verou]

Nevermind, it's ok now, tamarian fixed it :)