Finding the most appearing value for each column a value-CodePudding

I have a sheet with 2 columns, A which is a group name and B which is a Role name. Each Group appears multiple times with different roles, I want to find the most appearing role value in Column B for each Group value in column A. How can I achieve it using Google query ?

I didnt manage to succes, i tried using this google query :

=QUERY(A1:B, "SELECT b GROUP BY A HAVING COUNT(B) = MAX(COUNT(B))",1)

but I get an error:

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "HAVING "" at line 1, column 21. Was expecting one of: "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "," ... "" ... " " ... "-" ... "/" ... "%" ... "" ... "/" ... "%" ... " " ... "-" ...

CodePudding user response：

You can do a QUERY to count the number of appearences, but then (at least with the tools I have) you should filter the values to find the different MAX combinations:

The query being:

=QUERY(A:B,"SELECT A,B,COUNT(B) GROUP BY A,B")

And the complete formula:

=LAMBDA(quer,MAP(FILTER(UNIQUE(A:A),UNIQUE(A:A)<>""),
  LAMBDA(each,SORTN(FILTER(quer,INDEX(quer,,1)=each),1,0,1,1))))
(QUERY(A:B,"SELECT A,B,COUNT(B) GROUP BY A,B"))