I receive an error(ERROR: syntax error at or near "INTERSECT") in my postgresql query-CodePudding

I receive an error(ERROR: syntax error at or near "`INTERSECT`") in the SQL query, can someone help me to fix it?

The query is below:

SELECT *,
case when (Sum("col_name") OVER (INTERSECT select("col_name")) / Sum("col_name") OVER (All("col_name")))> 0.1 
then "col_name" else 'Other' end
FROM #table# AS "table"

Thanks in advance!!

I've tried to fix the query, nothing helped :(

CodePudding user response：

The INTERSECT operator has the following syntax:

query1 INTERSECT [ALL] query2

and

returns all rows that are both in the result of query1 and in the result of query2. Duplicate rows are eliminated unless INTERSECT ALL is used

You are using it in WINDOW function which is incorrect. I guess you are looking for something like this:

SELECT *
      ,CASE WHEN COUNT("col_name") OVER (PARTITION BY  "col_name") * 1.0 / COUNT(*) OVER () > 0.1 THEN CAST("col_name" AS VARCHAR(12)) ELSE 'other' END
FROM #table#

which for every row is giving the "colum_name" value if the count of the value is greater then 10% of the total rows in the table, otherwise - 'other'.

Also, you can replace the COUNT with SUM if that's what you need.

SELECT *
      ,CASE WHEN Sum("col_name") OVER (PARTITION BY  "col_name") * 1.0 / Sum("col_name") OVER () > 0.1 THEN CAST("col_name" AS VARCHAR(12)) ELSE 'other' END
FROM #table#