# Calculate avg age of multiple users?!?!

• Jan 29, 2011, 13:41
jmansa
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...

• Jan 29, 2011, 14:34
Cups
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.
• Jan 29, 2011, 17:45
jmansa
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?
• Jan 29, 2011, 20:30
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?
• Jan 30, 2011, 03:25
jmansa
Quote:

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?
• Jan 30, 2011, 03:42
jmansa
Quote:

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?
• Jan 30, 2011, 04:42
r937
could you dump a few rows of data from each table please, so we can test the query
• Jan 30, 2011, 04:52
jmansa
Quote:

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 cuINNER JOIN ".\$prefix."_users u ON cu.new_userid = u.new_useridWHERE cu.clubid='\$groupID' AND cu.active=0";  ```
• Jan 30, 2011, 07:31