I'm using SQLite database of the following sample rows with columns of {id, temperatures, description, time_stamp:
1 45 Clear 2016-01-13 10:24:17
2 45 Clear 2016-01-13 10:24:41
3 45 Clear 2016-01-13 10:24:41
4 45 Rain 2016-01-13 10:24:41
5 46 Clear 2016-01-13 10:38:29
6 46 Clear 2016-01-13 10:38:53
7 46 Clear 2016-01-13 10:39:08
8 46 Clear 2016-01-13 10:39:08
I run this query on them to group them by a time interval and weather description:
SELECT AVG(current_temperatures) AS temp_avg,
CASE WHEN strftime('%M', time_stamp) < '30'
THEN strftime('%H', time_stamp)
ELSE strftime('%H', time_stamp, '+1 hours') END as hour,
current_weather_description,
count(*) as counter
FROM weather_events
GROUP BY strftime('%H', time_stamp, '+30 minutes'), current_weather_description
order by hour desc
With the following results {row_num, temp_avg, hour, current_weather_description, counter}:
"46.0" "11" "Clear" "4"
"45.0" "10" "Clear" "3"
"45.0" "10" "Rain" "1"
My problem is how do I sub query each individual hour and group by the max counter. So ultimately I want to get the result:
"46.0" "11" "Clear" "4"
"45.0" "10" "Clear" "3"
I'm new to SQL and SQLite. All of the data comes from the same table. Also since the queried temperatures are averages, how does it choose the rest of the columns I ask it to select? For example if you select the time_stamp as well you'll see that it's a particular time_stamp from the database. Does it randomly choose which columns to select?
Aucun commentaire:
Enregistrer un commentaire