Filter out the result after count() function-CodePudding

How can I filter out the result after using count() function on that column? e.g. I am count the Id's, I want to filter out the ID that only counted once.

Thank you in advance :)

CodePudding user response：

To get entire rows with filter applied for Id column, you could use QUALIFY:

SELECT *, COUNT(*) OVER(PARTITION BY Id) AS cnt
FROM tab
QUALIFY cnt > 1;

CodePudding user response：

I prefer this over using a window function:

SELECT id, COUNT(*) as cnt
FROM tab
GROUP BY id
HAVING cnt > 1;