Get duration between records from separate tables by date

This is a bit tricky to explain but let's say i have 2 tables, one for the quotes and the other for the quotes responses.

Table: Quotes
-----------------
id  |  name  | date(DATETIME)
1   |  foo   | 2012-09-11 16:21:18
2   |  bar   | 2012-09-11 16:48:28

Table: Responses
-----------------
quote_id  |  response  |  date(DATETIME)
1         |  blah      |  2012-09-11 16:28:42
2         |  Meh       |  2012-09-11 16:58:34

I'd like to get the total average response rate to the quotes based on deducting the quote date off the response date.

So i get something like 8.7 seconds average response time.

How to do that efficiently using only a MySQL query if possible?

P.S. A Quote can have multiple responses, The calculation should only consi开发者_运维百科der the first response of each quote for an accurate result.

This joins your Quotes to the first date in Responses and gets the AVG TIMEDIFF. This will only consider those Quotes that have Responses.

SELECT AVG(TIMEDIFF(b.`date`, a.`date`))
FROM Quotes a
INNER JOIN (
  SELECT quote_id, MIN(`date`) as `date` 
  FROM Responses
  GROUP BY quote_id) b 
  ON (b.quote_id = a.quote_id) 

Will it work for you?

SELECT AVG(TIMEDIFF(b.`date`, a.`date`))
FROM Quotes a
INNER JOIN Responses b ON (b.quote_id = a.quote_id)