How to join unique strings in their own column with QUERY?

We have a sheet which contains all calendar events:

Sheet ‘CalendarEvents’

eventId  name       startDate        endDate            guest
A   Bob             10/7/2024 18:00  10/7/2024 19:00    bob@world.com       
B   Mike            10/9/2024 18:00  10/9/2024 19:00    mike@world.com      
C   Bob             10/9/2024 19:00  10/9/2024 20:00    bob@world.com       
D   Bob             10/9/2024 20:00  10/9/2024 21:00    bob@world.com       
E   Cancelled Mike  10/11/2024 16:00 10/11/2024 17:00   mike@world.com      
F   Other event     10/11/2024 16:00 10/11/2024 17:00

We have a pivot table that does what we need, but we need to replace it with a QUERY function, so it can be populated automatically when the App Scripts run. This doesn’t work for Pivot Tables unfortunately.

Sheet ‘PivotTable’ (desired result)

guest        eventCount evenTitle
                1       Other event 
bob@world.com   3       Bob
mike@world.com  2       Mike, Cancelled Mike

We are able to reproduce the first two columns correctly:

=QUERY(CalendarEvents!A5:Z, "SELECT E, count(E) GROUP BY E",1)

However, we can’t seem to get unique strings in the third column (eventTitle). We were hoping this would work, but it doesn’t:

=QUERY(CalendarEvents!A5:Z, "SELECT E, count(E), STRING_AGG(DISTINCT B, ', ') GROUP BY E",1)

Error: Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "(" "( "" at line 1, column 31. Was expecting one of:

Here is a test sheet.

Thank you for your help.

>Solution :

Here’s a generalized approach which you may adapt accordingly:

=query({B6:B,E6:E,map(E6:E,lambda(Σ,textjoin(", ",1,unique(filter(B6:B,E6:E=Σ)))))},
 "Select Col2,count(Col1),Col3 Where Col1!='' group by Col2,Col3 label count(Col1) ''",0)