CODEWITHSUNDEEP
postgresqlsubquerycommon-table-expression
Ruslan
Ruslan

Reputation: 951

PostgreSQL creating a subtable and using with clause

I am trying to test users for a condition, then select all events of the main table that match the condition. I feel like I am doing something wrong:

WITH users_table AS (
    SELECT
        user_name,
        SUM (CASE WHEN ( ud.age > 20 ) THEN 1  ELSE 0 END) AS UserEvents
    FROM users_data AS ud
    GROUP BY user_name
    )
SELECT users_table.user_name FROM users_table
WHERE users_table.UserEvents > 10
;

That is, I want to use the with clause to make a table of all the users above the age of 20, then of that table select only use users that has more than 10 events.

For some reason I keep getting the error:

ERROR: multiple WITH clauses not allowed. SQL state: 42601

But I don't understand why? Also, why "multiple" with clauses...? There's only one with clause.

Best wishes, -R

Upvotes: 1

Views: 3051

Answers (1)

Nick
Nick

Reputation: 7441

When you are using multiple common table expressions, you can only use the WITH keyword once. So it should look like:

WITH first_cte AS (
   SELECT ...
),
second_cte AS (
   SELECT ...
)

Upvotes: 2

Related Questions