# Thread: Calculate avg age of multiple users?!?!

1. ## Calculate avg age of multiple users?!?!

I'm trying to figure ot the avareage age of all users in a certain group.

This is how I calculate 1 user:
PHP Code:
\$sql="SELECT * FROM ".\$prefix."_users WHERE group = 1";
\$result mysql_query(\$sql);
while(
\$row mysql_fetch_array(\$result)){

\$birthday \$row['birthday']; // 1970-05-08 //

list(\$year,\$month,\$day) = explode("-",\$birthday);

\$year_diff  date("Y") - \$year;

\$month_diff date("m") - \$month;

\$day_diff   date("d") - \$day;

echo
'Age: '.\$year_diff;

As you can see I calculate the age outside the query... I think I have to doit within the query...

Any help is apreciated...

2. Code:
\$sql = "SELECT
AVG( YEAR(now()) - YEAR(birthday)) as avg_age
FROM ".\$prefix."_users
WHERE group = 1" ;
*Only very roughly tested*

Need to use ceil() to round up decimal points.

3. I have tryied this, but with no luck:
PHP Code:
\$sql="SELECT AVG(YEAR(NOW()) - YEAR(u.birthday)) AS avg_age FROM ".\$prefix."_club_users cu
INNER JOIN "
.\$prefix."_users u ON cu.new_userid = u.new_userid
WHERE clubid='
\$umode' AND cu.active=0";
\$result mysql_query(\$sql) or die("Error: (" mysql_errno() . ") " mysql_error());
\$row mysql_fetch_array(\$result);
Any ideas anybody?

4. i'm sorry, i am not familiar with the "with no luck" mysql error message

didn't it say anything a bit more substantial?

5. Originally Posted by r937
i'm sorry, i am not familiar with the "with no luck" mysql error message

didn't it say anything a bit more substantial?
Sorry, but it didnt give any errors... Just no output

PHP Code:
\$sql="SELECT AVG(YEAR(NOW()) - YEAR(u.birthday_new)) AS avg_age FROM ".\$prefix."_club_users cu
INNER JOIN "
.\$prefix."_users u ON cu.new_userid = u.new_userid
WHERE clubid='
\$umode' AND cu.active=0";
\$result mysql_query(\$sql) or die("Error: (" mysql_errno() . ") " mysql_error());
\$row mysql_fetch_array(\$result);

echo
'Average age: '.\$row['avg_age'];
This just gives
Average age:

Any ideas?

6. Originally Posted by jmansa
Sorry, but it didnt give any errors... Just no output

PHP Code:
.......
echo
'Average age: '.\$row['avg_age'];
This just gives
Average age:

Any ideas?
Sorry, my bad. It gives an output:
Average age: 534.5000

But thats totally wrong! It should give 41,5?

Any ideas?

7. could you dump a few rows of data from each table please, so we can test the query

8. Originally Posted by r937
could you dump a few rows of data from each table please, so we can test the query
Sorry, one record was wrong (000-00-00). Everything works fine with query below... But is there a way to not count the ones with 0000-00-00?

PHP Code:
\$sql="SELECT AVG(DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(u.birthday_new, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(u.birthday_new, '00-%m-%d'))) AS avg_age, COUNT(*) as count FROM fb_club_users cu
INNER JOIN "
.\$prefix."_users u ON cu.new_userid = u.new_userid
WHERE cu.clubid='
\$groupID' AND cu.active=0"

9. change 0000-00-00 to NULL and they won't be counted.

10. Here is a link to calculate age in the SQL itself in MySQL's documentation.

Just tweak it for your use.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•