I am trying to write a query using a CASE statement that is based off whether a value is in array, but I run into the catch 22 of not including the case statement in the GROUP BY and getting “must be an aggregate expression or appear in GROUP BY clause” or including it in the GROUP BY and getting “GROUP BY clause cannot contain aggregations, window functions or grouping operations” so I know I’m missing something. My query is:

Select
    title,
    owner,
    CASE
        WHEN CONTAINS(SET_AGG(team_id, 1234) then project_name
        ELSE array_join(set_agg(project_name), CHR(10))
    END AS project_name
FROM actions
GROUP BY 1,2

Actions can belong to more than one team, but if I know it’s part of a specific team, I want to use that project name, otherwise I just want to return all the project names. Some example date would be

and the return should be:

I tried grouping by just the first two columns and grouping by all three columns, and it errors both ways.