How to implement conditional COUNTIF in SQL?

Question

I am new to SQL and facing the problem for the while. The question is how to create such Label column in SQL?

Goal: When group count is more than 1 it should be 'Y' otherwise 'N'

Here is what I do in Excel (please see screenshot).

Answer 1

I think you want case with a window function:

select (case when count(*) over (partition by item) > 1 then 'Y' else 'N' end) as label