How can I make a GROUP BY on a time period?

Question

I have table with Timestamp, temperature and humidity. I want to have the average temperature in the periods where the humidity is above 80%. Something like

SELECT MIN (DTM), MAX (DTM), AVG(TMP) GROUP BY (WHERE HMD>80)

Is that possible?

Thank you

Hugo

What I need is to define periods that start when the humidty rise above 80% and end when it comes bellow. Then I have to get the BEGIN timestamp the END timestamp and the average temperature

You want to group by what? You should have distinct concrete values to group by e.g. day of timestamp. — ahmet alp balkan, May 27 '11 at 19:32
So you're looking for a range or ranges of consecutive time stamps where the humidity remains above 80% during the entire range? — Joe Stefanelli, May 27 '11 at 19:33

score 3 · Accepted Answer · answered May 27 '11 at 19:33

3

In the Group BY Clause you need to use CASE instead of WHERE :

SELECT MIN (DTM), MAX (DTM), AVG(TMP) GROUP BY (CASE WHEN HMD>80 THEN HMD END);

answered May 27 '11 at 19:33

aleroot

71,077
30
176
213

score 1 · Answer 2 · edited May 23 '17 at 12:26

1

More like:

SELECT MIN (DTM), MAX (DTM), AVG(TMP) FROM tbl WHERE HMD>80 GROUP BY location

Your main issue is going to extract the valid range, though, because HMD > 80 will return all rows that match the criteria whether they're in the same period or not.

If you actually need to extract valid ranges beforehand you might want to visit this thread:

Get list with start and end values from table of datetimes

edited May 23 '17 at 12:26

Community

1
1

answered May 27 '11 at 19:35

Denis de Bernardy

75,850
13
131
154

Where did `location` come from? – Joe Stefanelli May 27 '11 at 19:37
It comes from nowhere. His query is invalid anyway, since it has no `from` clause. But I'm assuming he's trying to average per city/area/location/something. – Denis de Bernardy May 27 '11 at 19:39

How can I make a GROUP BY on a time period?

2 Answers2