Google Sheets query Select column concat with Select Count column

Question

I help manage loot for a group in an MMO. I've created a google sheet to maintain visibility into who needs what items. The sheet basically works the way i want, but i'm having some trouble with returning SELECT A concatenated with SELECT COUNT(A). Honestly, i'm not sure how to go about it, but this is what i've got:

Right now I'm transposing

SELECT A WHERE C CONTAINS <VALUE>

which returns like this for multiple results:

Name OtherName OtherName

Meanwhile, the values (but not the format) i want to return, i can get with :

SELECT A, COUNT(A) WHERE C CONTAINS <VALUE> GROUP BY A LABEL COUNT(A) ''"

which returns:

Name OtherName

1 2

The desired output is:

Name(1) Othername(2)

The sheet is here:

https://docs.google.com/spreadsheets/d/1HUcgVg2ZFHteXhRnk2W2j_9RU4RvMd7nMTqCuRgmUdo/edit?usp=sharing

And the queries in question are on Sheet1. I put some example queries I've tried also in the sheet.

Answer 1

try:

=INDEX(REGEXREPLACE(QUERY(TRANSPOSE(IFNA(IF(B11>0, QUERY(GearList!A:C, 
 "select A,count(A) 
  where C contains '"&$D11&"' 
  group by A
  label count(A)''", 0)), )),,99^99), " (\d+)", "($1)"))