sql - How to aggregate the daily temperature data into weekly data? -

sql - How to aggregate the daily temperature data into weekly data? -

i aggregate daily temperature info weekly data. uses 3 select statements. there way combine them one?

create table dailyx ( city integer, -- index of city day date, -- date in yyyymmdd format first float, -- first temperature reading day high binary_double, -- high temperature low binary_double, -- low temperature lastly float, -- lastly temperature reading day count integer -- number of readings );

this stmt sums daily info weekly time frame finding highest , lowest temperature week, , summing number of readings.

select city,to_char(day,'iw'), max(high), min(low), sum(nvl(count,0)) dailyx day > to_date(20140101,'yyyymmdd') grouping city,to_char(day,'iw');

this stmt find first reading of week first reading on monday.

select p_city,p_week,p_first (select city p_city, to_char(day,'iw') p_week,first p_first, rank() on (partition city,to_char(day,'iw') order day asc) rnk dailyx day > to_date(20140101,'yyyymmdd')) rnk=1;

this stmt finds lastly reading of week lastly reading on friday.

select p_city,p_week,p_last (select city p_city, to_char(day,'iw') p_week,last p_last, rank() on (partition city,to_char(day,'iw') order day desc) rnk dailyx day > to_date(20140101,'yyyymmdd')) rnk=1;

you can utilize keep:

select city, to_char(day,'iw'), max(high), min(low), sum(nvl(count,0)), max(first) maintain (dense_rank first order day) first, max(last) maintain (dense_rank lastly order day) lastly dailyx day > to_date(20140101,'yyyymmdd') grouping city, to_char(day,'iw');

sql oracle