title: How to get min max and avg from table in mysql with different rows?
body: <p>i have a table with record given below. What i want is single select query which shows first record min second record avg and third record max value group by mediaid? </p>
  
  <pre><code>trans_id      playtime       mediaid(F.K.) 
  --------      --------       -------------
  1              32             2
  2              65             2
  3              45             3
  4              55             4
  5              60             3
  6              78             2
  7              45             4
  8              35             3
  </code></pre>
  
  <p>result should look like </p>
  
  <pre><code> playtime       mediaid(F.K.) 
     --------       -------------
      32             2             (Min value)
      58.33          2             (avg value)
      78             2             (Max value)
      35             3             (Min value)
      46.66          3             (avg value)
      60             3             (Max value)
      45             4             (Min value)
      50             4             (avg value)
      55             4             (max value)
  </code></pre>
  
  <p>this is my real query giving the result but this is not arranged by medtitle </p>
  
  <pre><code>select  t.medTitle as 'Media',
      min(TIMESTAMPDIFF(SECOND,s.dmtViewStart,s.dmtViewEnd))platime
          from dbedetailing.tblmedia t
          left outer join dbedetailing.tbldoctormediatracktest s on s.dmtMedId = t.medId 
          GROUP BY t.medTitle
          union all
          select  t.medTitle as 'Media',
      avg(TIMESTAMPDIFF(SECOND,s.dmtViewStart,s.dmtViewEnd))platime
          from dbedetailing.tblmedia t
          left outer join dbedetailing.tbldoctormediatracktest s on s.dmtMedId = t.medId 
          GROUP BY t.medTitle
          union all
          select  t.medTitle as 'Media',
      max(TIMESTAMPDIFF(SECOND,s.dmtViewStart,s.dmtViewEnd))platime
          from dbedetailing.tblmedia t
          left outer join dbedetailing.tbldoctormediatracktest s on s.dmtMedId = t.medId 
          GROUP BY t.medTitle ;
  </code></pre>
  
  <p>this is my real query giving the result but this is not arranged by medtitle </p>
  
  <p>===Database dbedetailing</p>
  
  <p>== Table structure for table tbldoctormediatracktest</p>
  
  <pre><code>|------
  |Column|Type|Null|Default
  |------
  
      |//**dmtId**//|int(10)|No|
      |dmtDocId|int(10)|No|
      |dmtMedId|int(10)|No|
      |dmtViewingDate|datetime|No|
      |dmtViewStart|datetime|Yes|NULL
      |dmtViewEnd|datetime|Yes|NULL
      |dmaIsActive|enum('0', '1')|No|
      |insDate|datetime|No|
      |modDate|datetime|No|
      == Dumping data for table tbldoctormediatracktest
    Medid    dmtViewStart         dmtViewEnd
      |56|2012-08-24 11:41:14|2012-08-24 11:41:20
      |52|2012-08-24 11:41:24|2012-08-24 11:42:12
      |56|2012-08-24 11:41:14|2012-08-24 11:41:20
      |52|2012-08-24 11:41:24|2012-08-24 11:42:00
      |53|2012-08-25 12:32:27|2012-08-25 12:32:42
      |55|2012-08-27 06:48:51|2012-08-27 06:49:19
      |53|2012-08-27 06:49:25|2012-08-27 06:49:42
      |53|2012-08-28 02:56:16|2012-08-28 02:56:47
      |54|2012-08-28 02:57:58|2012-08-28 02:58:10
      |52|2012-08-28 05:48:14|2012-08-28 05:49:03
      |55|2012-08-28 07:16:00|2012-08-28 07:17:11
      |52|2012-08-22 04:03:00|2012-08-22 04:03:55
      |56|2012-08-28 07:16:00|2012-08-28 07:17:22
      |56|2012-08-23 09:00:00|2012-08-23 09:02:00
      |53|2012-08-16 04:00:00|2012-08-16 04:01:01
      |54|2012-08-20 12:33:00|2012-08-20 12:34:14
      |55|2012-08-16 04:00:20|2012-08-16 04:01:01
      |52|2012-08-20 12:31:00|2012-08-20 12:31:14
      |54|2012-08-20 12:33:14|2012-08-20 12:34:14
      |55|2012-08-20 12:48:00|2012-08-20 12:49:14
      |56|2012-08-20 12:33:00|2012-08-20 12:34:14
      |53|2012-08-20 12:43:00|2012-08-20 12:44:14
      |54|2012-08-20 12:33:00|2012-08-20 12:34:45
      |55|2012-08-20 12:33:00|2012-08-20 12:34:34
      |56|2012-08-20 12:33:00|2012-08-20 12:34:23
      |52|2012-08-20 12:33:00|2012-08-20 12:34:18
      |53|2012-08-20 12:33:00|2012-08-20 12:34:56
      |54|2012-08-20 12:33:00|2012-08-20 12:33:56
      |56|2012-08-20 12:33:00|2012-08-20 12:33:14
      |56|2012-08-20 12:33:00|2012-08-20 12:34:14
  
  
  
  ===Database dbedetailing
  
  == Table structure for table tblmedia
  
  |------
  |Column|Type|Null|Default
  |------
  |//**medId**//|int(10)|No|
  |medTitle|varchar(250)|No|
  |medDesc|varchar(250)|No|
  |medPath|varchar(250)|No|
  |medType|int(10)|No|
  |medSpeciality|int(10)|No|
  |medProduct|int(10)|No|
  |medIsActive|enum('0', '1')|No|
  |insDate|datetime|No|
  |modDate|datetime|No|
  == Dumping data for table tblmedia
  </code></pre>
  
  <p>===Database dbedetailing</p>
  
  <pre><code>== Dumping data for table tblmedia
  
  |51|Tplanin
  |52|Casfung
  |53|Knee Surgery
  |54|Doriglen
  |55|Colymonas
  |56|inem
  |57|Casfungtest
  </code></pre>