mercredi 13 janvier 2016

SQLite: Subquery to group by max count

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