Check Distinct value Present in the group

Question

I have a table with multiple pos and I need to find the Purchase Id where it has only wallet per group and nothing else in the group.

For eg,here PID - 4 and 5 has only wallet , rest has other's as well. So wallet_flag should be 1 in the output.

I tried to use window's function but could not achieve the result. Can you please suggest.

select PID 
      ,POS
     , SUM(CASE WHEN POS='bwallet' THEN 1 ELSE 0 END ) OVER(PARTITION BY PID)  as FLAG
                             
from PAYMENTS
where "status"  = 'SUCCESS'  

OUTPUT:

Answer 1

SELECT
  your_table.*,
  MIN(
    CASE pos
      WHEN 'wallet' THEN 1
                    ELSE 0
    END
  )
  OVER (
    PARTITION BY pid
  )
    AS wallet_flag
from
  your_table

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e05a7863b9f4d912dcdf5ced5ec1c1b2