title: MySql subquery: average difference, grouped by column
body: <p>I have a mysql table with visitor_id, country, time_of_visit.</p>
  
  <p>I want to get the average duration of visit by country.</p>
  
  <p>To get duration, I get the difference between the earliest and latest time_of_visit for each visitor_id.</p>
  
  <p>So this gets me the average duration of all visits:</p>
  
  <pre><code>    SELECT AVG(duration)
      FROM 
      (
      SELECT TIMESTAMPDIFF(SECOND, MIN(time_of_visit), MAX(time_of_visit))/60 
  as duration 
      FROM tracker 
      GROUP BY visitor_id
      ) as tracker
  </code></pre>
  
  <p>That works. But to group it by country, I fail. Here's my most recent attempt to get average duration by country:</p>
  
  <pre><code>SELECT country, AVG(duration)
  FROM 
  (
  SELECT TIMESTAMPDIFF(SECOND, MIN(time_of_visit), MAX(time_of_visit))/60 
  as duration 
  FROM tracker 
  GROUP BY visitor_id
  ) as tracker
  GROUP BY country
  </code></pre>
  
  <p>The error I get is: Unknown column 'country' in 'field list'.</p>
  
  <p>I think this should be simple, but I'm a noob. I searched a lot, tried lots of ideas, but no good. Any help?</p>
  
  <p>Thanks in advance.</p>