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...

Thanks in advance ;-)

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.

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?

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

didn't it say anything a bit more substantial?

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?

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?

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

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 cu
INNER JOIN ".$prefix."_users u ON cu.new_userid = u.new_userid
WHERE cu.clubid='$groupID' AND cu.active=0"; 


---

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

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

Just tweak it for your use.