Search code examples
sqldatetimegoogle-sheetstimegrouping

Grouping data hour-wise using Query Function in Google Sheets

I have 2 columns in my google sheet - Time, and some Ids

enter image description here

My aim is to calculate the number of ids reported in an hour. For example, from this image we can tell from 10AM to 11AM - 4 ids, and from 11AM to 12PM - 5 ids. I want to come up with a QUERY Function ONLY that helps me do so, and group the number of IDs hour-wise. Any help would be much appreciated.

Solution

  • use:

    =QUERY(QUERY(A2:B, 
 "select hour(A),count(B) where B is not null group by hour(A)"), 
 "offset 1", )

    enter image description here