Group by in Mysql & Drop or Create table

Group By  : -

Example : I need fetch avg time between 08:00:00 to 09:00:00    

# STATION_ID, DATA_DATE, HUMIDITY
'9201', '2020-01-01 00:00:00', '93'
'9201', '2020-01-01 01:00:00', '93'
'9201', '2020-01-01 02:00:00', '94'
'9201', '2020-01-01 03:00:00', '94'
'9201', '2020-01-01 04:00:00', '95'
'9201', '2020-01-01 05:00:00', '95'
'9201', '2020-01-01 06:00:00', '96'
'9201', '2020-01-01 07:00:00', '96'
'9201', '2020-01-01 08:00:00', '96'
'9201', '2020-01-01 09:00:00', '96'
'9201', '2020-01-01 10:00:00', '96'
'9201', '2020-01-01 11:00:00', '95'
'9201', '2020-01-01 12:00:00', '95'
'9201', '2020-01-01 13:00:00', '95'
'9201', '2020-01-01 14:00:00', '94'
'9201', '2020-01-01 15:00:00', '92'
'9201', '2020-01-01 16:00:00', '89'
'9201', '2020-01-01 17:00:00', '89'
'9201', '2020-01-01 18:00:00', '90'
'9201', '2020-01-01 19:00:00', '91'
'9201', '2020-01-01 20:00:00', '93'
'9201', '2020-01-01 21:00:00', '93'
'9201', '2020-01-01 22:00:00', '94'
'9201', '2020-01-01 23:00:00', '96'   

Example:- Filter data 08:00:00 and 09:00:00

select ID,avg(humidity) as HR_830 from ( select id,humidity,Time(datadate) as datatime from WEATHER_DATA where Time(data_date)>='08:00:00' and Time(data_date)<='09:00:00'  ) T1 group by id;

example

#Retrive data from date wise and use ID or Count data per day

select date(DATA_DATE),ID,count(*) from WEATHER_DATA
where ID in (11204,10980) and DATE between '2020-07-01 00:00:00'
and '2020-08-24 23:50:50'
group by date(DATE),ID
 

 

#   ID,      DATA_DATE                Temp_Max   Temp_Min

    '89',  '2020-08-20 00:00:00',     '22.28'         '22.36'  

    '89',  '2020-08-20 01:00:00',     '25.59'          '25.71' 

    '89',   '2020-08-20 02:00:00',     '24.77'         '25.25'   

    '89',   '2020-08-20 03:00:00',     '22.47'         '22.68'   

    '89',   '2020-08-20 04:00:00',     '29.67'         '30.25'  

    '89',   '2020-08-20 05:00:00',     '23.86'         '24.13'

    '75',  '2020-08-20 00:00:00',     '22.28'         '22.36'  

    '75',  '2020-08-20 01:00:00',     '25.59'          '25.71' 

   '75',   '2020-08-20 02:00:00',     '24.77'         '25.25'   

   '75',   '2020-08-20 03:00:00',     '22.47'         '22.68'   

   '75',   '2020-08-20 04:00:00',     '29.67'         '30.25'  

   '75',   '2020-08-20 05:00:00',     '23.86'         '24.13'

Note : Group by always used with aggregate function like MAX() ,MIN(),AVG()

Table column name is ID and DATA_DATE or Temp_Max,Temp_Min .So we have to convert to DATA_DATE in DATE .

select ID ,DATE(DATA_DATE) ,max( Temp_Max ) ,min(Temp_Min)  from     workstation  group by DATE(DATA_DATE) ;

Output:- 

# ID,      DATE           Temp_Max,    Temp_Min

    89     2020-08-11          '26.68'      '26.88'

 It is give only one ID data .If you pass also ID in group by statement . return all data all station and date wise.

example:

select ID ,DATE(DATA_DATE) ,max( Temp_Max ) ,min(Temp_Min)  from    workstation  group by  ID  ,  DATE(DATA_DATE) ;

Output:-

# ID,      DATE              Temp_Max,    Temp_Min
    75', '2020-08-11          '25.02',          '25.25'

    89', '2020-08-11          '26.68'           '26.88' 

 It is give output all ID base or date wise data.

Real World Example   

drop table if exists test.Temperature_data ;

create table test.Temperature_data  

select  id , date(start_date) as datadate,max(Temp) as Tmax,min(Temp) as Tmin,max(HUMIDITY) H_Max,round(min(HUMIDITY),2) H_MIN,max(WIND_MAX) as Max_wind,sum(RAIN) Total_Rain from  weather_data  group by station_id,date(Start_date);