Struggling with repeating query error - "Parameter 2 cannot be in group by"-CodePudding

=ARRAYFORMULA(TEXT(QUERY(Event!A:Z,"select B,COUNT(B),C where C>=date'"&A1&"' and C<=date'"&A2&"' group by count(b) order by COUNT(B) desc label B 'Usernames', C 'Date', COUNT(B) 'Frequency'",0),"#"))

The columns I'm referencing are like this (screenshot: https://gyazo.com/c0b0098da3b50f01fc1d40e769495b72 ) and so on. The date values are correct and work when tested, but when I attempt to count the number of responses per username per week-long period, it continues to give me this error:

Unable to parse query string for Function QUERY parameter 2: CANNOT_BE_IN_GROUP_BY: COUNT(b)

I've gotten this working before in the past, but scrapped it all since I decided to do what I was working on in a different way. This is annoying me because it's not working when a couple hours ago it was

CodePudding user response：

try:

=ARRAYFORMULA(TEXT(QUERY(Event!A:Z,
 "select B,count(B),C 
  where C >= date '"&A1&"' 
    and C <= date '"&A2&"' 
  group by B,C
  order by count(B) desc 
  label B 'Usernames',C 'Date',count(B)'Frequency'", 0), "#"))

CodePudding user response：

The issue here is that group by has to be followed by an array, a “list” of elements. However, you are currently feeding the group by with a number, which is what count(B) returns. You should replace count(B) with something like B, which returns a list; and since later on you want to use a label in the C column, you should group by B,C, so your code could end up looking like:

=ARRAYFORMULA(TEXT(QUERY(Event!A:Z,"select B,COUNT(B),C where C>=date'"&A1&"' and C<=date'"&A2&"' group by B,C order by B,C desc label B 'Usernames', C 'Date', COUNT(B) 'Frequency'",0),"#"))