Grouping data hour-wise using Query Function in Google Sheets-CodePudding

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

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.

CodePudding user response：

Use this

=ArrayFormula({"Hour","Id's";UNIQUE(HOUR(A1:A18)),COUNTIF(IF(B:B="",,HOUR(A:A)),"="&UNIQUE(HOUR(A1:A18))&"")})

Using Lambda

You need to set the range refrence once, like this example A:B.

=ArrayFormula(
      LAMBDA(r, LAMBDA(t,i,
              {"Hour","Id's";UNIQUE(t),COUNTIF(t,"="&UNIQUE(t))})
                      (HOUR(INDEX(r,,1)),INDEX(r,,2)))
            (QUERY({A:B}," where Col1 is not null ")))

Explanatoin

What we did is Query where Col1 non empty in the first lambda call and
in the second lambda we name col1 t for time with the call HOUR(INDEX(r,,1))
and col1 i for id's with the call INDEX(r,,2)
and we use the two columns in COUNTIF that can be simplified like this.

COUNTIF(t,"="&UNIQUE(t))

Now we join UNIQUE(t) and COUNTIF in an Array {}

{"Hour","Id's";UNIQUE(t),COUNTIF(t,"="&UNIQUE(t))}

CodePudding user response：

use:

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