sqlite - Group resulting rows by hour of the day sqllite android -

there entries in table containing 2 columns (integerdata, time). time stored in text , of format hh:mm, 1 of standard format ( according sqllite documentation). 'integerdata' integer. looking obtain row contents grouped hour of day, integerdata avg(integerdata) each hour. eg rows:

(10, 10:01) (2, 10:25) (5, 11:01) (9, 11:25) 

i wish have output like:

6, 10:00  7, 11:00 

one novice , horrible way give query each hour range(making 24 queries). like:

select avg(integerdata) table time between 'time1' , 'time2' //time1 , time2 can vary between 10:00 11:00; 11:00 12:00 ... 

second way might use group clause, not sure how. googled , tried on answers on stackoverflow of no help. appreciate contribution. thanks!

i looking obtain row contents grouped hour of day, integerdata avg(integerdata) each hour.

here first idea. since time still text, can use substr() function hour time column , can use substring in group by clause.

select avg(integerdata), substr(time, 0, 3) gt table group gt; 

i assume time format hh:mm in case substr(time, 0, 3) returns hours , can start grouping hours.

it returns: 10, 11, 12, ...