Averages between two tables with a many-to-one relationship

[jenni]

I have a table of requests and a table of notes on those requests. I need to be able to count the number of requests and then display an average amount of time from when the request came in to when the last note was placed. If worse comes to worse, I can just add a field to the request table for lastModified. I would just prefer to do it through an sql statement if possible in case they want other averages.

Here is an sql statement to play with. I actually join other tables, but thought it might be even more confusing to work with.

SELECT 
DATE_FORMAT(requests.date,'%b %D') as day,
COUNT(*) as requestTotal 
FROM requests as requests 
WHERE requests.date>='2005-10-1' AND requests.date<='2005-11-30' 
Group by DAYOFMONTH(requests.date) 

It is kind of a longshot, but I thought it couldn't hurt to try the forums. Thanks for your help.

[Najjar]

I'm not sure if I understand your request and don't know what language you're using!
What I would do is get the values of these requests and change the firstdate and lastdate
then convert them seconds, devide by 2 to get the average then convert the
returnning value to date again.

I think that using the LastModefied field is way better and more prof.

[jenni]

I am using PHP and mySQL and I gather by the flurry of responses that doing it all through an SQL query is wishful thinking. I will have to do it the other way.

Thanks for your response.