Skip to content
Advertisement

How to select other rows in GROUP BY

Sorry for the bad title. I don’t know how to summarize it better. I have a table with unix timestamps and other values, like the following.

time value
1620916380 110
1620916440 100
1620916500 120
1620916560 120
1620916660 90

The entries are always one minute apart. I want to group entries into 5-minute blocks with the value averaged. Currently I use this:

SELECT time, avg(value)
FROM table
GROUP BY CEIL(time/ 300)
ORDER BY time ASC;

The result:

time avg(value)
1620916380 108

So, for time the first occurrence is shown. I want the last to be shown:

time avg(value)
1620916660 108

How can I do that? I use MariaDB.

Advertisement

Answer

Don’t you just want MAX(time)?

SELECT MAX(time) AS time
     , avg(value)
  FROM table
 GROUP BY CEIL(time/ 300)
 ORDER BY time ASC
;

For completeness, we should mention that the original query is not valid (per standard SQL), even though MariaDB can be configured to allow it.

The reason is time in the select list is not functionally dependent on the GROUP BY terms CEIL(time/300).

Selecting MAX(time) AS time in the solution corrects the above functional dependence issue, and also corrects a similar issue when trying to use ORDER BY time, which was also a problem in the original query.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement