CODEWITHSUNDEEP
mysql
Manish Malviya
Manish Malviya

Reputation: 871

How to get min max and avg from table in mysql with different rows?

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? 

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

result should look like 

 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)

this is my real query giving the result but this is not arranged by medtitle 

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 ;

this is my real query giving the result but this is not arranged by medtitle

===Database dbedetailing

== Table structure for table tbldoctormediatracktest

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

===Database dbedetailing

== Dumping data for table tblmedia

|51|Tplanin
|52|Casfung
|53|Knee Surgery
|54|Doriglen
|55|Colymonas
|56|inem
|57|Casfungtest

Upvotes: 1

Views: 8287

Answers (1)

Taryn
Taryn

Reputation: 247710

If you want the values in a single row, you can do this:

select max(playtime), min(playtime), avg(playtime)
from yourtable
group by mediaid;

If you want them in separate rows, use a UNION ALL:

select max(playtime) playtime, mediaid, 'Max' Type
from yourtable
group by mediaid
union all
select min(playtime) playtime, mediaid, 'Min'
from yourtable
group by mediaid
union all
select avg(playtime) playtime, mediaid, 'Avg'
from yourtable
group by mediaid
order by mediaid, playtime;

see SQL Fiddle with Demo

Edit #1, using your sample data added:

select m.medTitle, 
  min(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime
from tbldoctormediatracktest s
left join tblmedia m
  on s.MedId = m.MedId
group by m.medTitle
union all

select m.medTitle, 
  avg(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime
from tbldoctormediatracktest s
left join tblmedia m
  on s.MedId = m.MedId
group by m.medTitle
union all

select m.medTitle, 
  max(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime
from tbldoctormediatracktest s
left join tblmedia m
  on s.MedId = m.MedId
group by m.medTitle
order by medTitle,  playtime

See SQL Fiddle with Demo

Upvotes: 8

Related Questions