I need help with the following query.

I have the following table with information,

I tried the following query but I don't get the expected result,

SELECT
    MIN(date) as min_date,
    MAX(date) as max_date,
    COUNT(*) as count,
    temperature
FROM
  temperatures
GROUP BY
   temperature
ORDER BY
   id;

Obtained result

Expected result

I appreciate the help you can give me if someone has more clear how I could get the expected result.

Greetings,

CodePudding user response：

Without further elaboration on your expected output, I can only assume based on the output provided, you intend to group by temperature first,then by year_month_day_hour. If so,try this:

SELECT
    MIN(date) as min_date,
    MAX(date) as max_date,
    COUNT(*) as count,
    temperature
FROM
  temperatures
GROUP BY
   temperature,date_format(date,'%y%m%d%H')
ORDER BY
    date_format(date,'%y%m%d%H');

-- result set:
# min_date, max_date, count, temperature
2000-10-01 05:20:00, 2000-10-01 05:30:00, 2, 12
2000-10-01 05:00:00, 2000-10-01 05:10:00, 2, 10
2000-10-01 05:40:00, 2000-10-01 05:50:00, 2, 13
2000-10-01 06:00:00, 2000-10-01 06:10:00, 2, 10

CodePudding user response：

With the use of ntile I grouped the rows in pairs starting on the first one and ordering it by id.

select cast(count(*) over ()/2 as unsigned) into @cnt
from t
limit 1;

select    min(date)        as min_date
         ,max(date)        as max_date
         ,count(*)         as count
         ,avg(temperature) as temperature
from     (
          select *, ntile(@cnt) over (order by id) as pairs
          from t
         ) t
group by pairs

Fiddle