How to use CASE with CONTAINS and Array Aggregation

Question

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

title	owner	team_id	project_name
test1	owner1	1234	ProjectA
test2	owner1	1234	ProjectB
test2	owner1	6789	ProjectC
test3	owner2	4567	ProjectD
test3	owner2	6789	ProjectE

and the return should be:

title	owner	project_name
test1	owner1	ProjectA
test2	owner1	ProjectB
test3	owner2	ProjectD ProjectE

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

Is there a parenthesis missing here SET_AGG(team_id, ? All documenation I could find shows SET_AGG as an aggregation function taking one parameter. — tinazmu
– tinazmu, Commented Aug 19, 2024 at 22:27
Where did the result row (test2, owner2) come from? no such combination in your sample data. — tinazmu
– tinazmu, Commented Aug 19, 2024 at 22:39
@tinazmu Added the missing parenthesis and fixed owner2 to be owner1 — BigPanda
– BigPanda, Commented Aug 19, 2024 at 22:51
@nbk what sort of aggregate function? I tried using MAX and got the error "Cannot nest aggregations inside aggregation 'max':" — BigPanda
– BigPanda, Commented Aug 19, 2024 at 22:51

Vijay Panwar · Accepted Answer · 2024-08-20 01:27:06Z

0

I can see what you trying to do.

The issue is you are trying to use an aggregate function (SET_AGG) inside a CASE statement, which is not allowed. Additionally, you can't include the CASE statement in the GROUP BY clause because it's not a column or an aggregate function.

Here is a possible solution.

SELECT
    title,
    owner,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM actions a2
            WHERE a2.team_id = 1234
            AND a2.title = actions.title
            AND a2.owner = actions.owner
        ) THEN  (SELECT array_join(ARRAY_AGG(project_name), CHR(10)) FROM actions a3 WHERE a3.title = actions.title AND a3.owner = actions.owner AND a3.team_id != 1234)
    ELSE array_join(ARRAY_AGG(project_name), CHR(10))
    END AS project_name
FROM actions
GROUP BY 1, 2

I replaced SET_AGG with ARRAY_AGG, which is an aggregate function that returns an array of values.
I moved the CASE statement outside the aggregate function.
I used EXISTS clause to check there at least on row in the actions table that matches the specific team ID (1234).

edited Aug 20, 2024 at 1:27

answered Aug 19, 2024 at 22:54

Vijay Panwar

2962 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BigPanda Over a year ago

Thanks for the response! When I modified my query to look like yours, I am still getting project_names for other team_ids when an action has the specific team_id I'm looking for. Using my sample date, I would still get a row for test2, owner2, ProjectC tied to team_id 6789 even though I only want the ProjectB tied to team_id 1234

Vijay Panwar Over a year ago

I see your point, I made the changes in query, to achieve this, you can use a subquery to get the list of project_name values for each title and owner combination. I am assuming that project_name is a column in the actions table. If that's not the case, please modify it as needed.

Collectives™ on Stack Overflow

How to use CASE with CONTAINS and Array Aggregation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related