Check Distinct value Present in the group-CodePudding

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:

CodePudding user response：

Here's one option:

Sample data:

SQL> with test (pid, pos, amount) as
  2    (select 1, 'wallet',  10 from dual union all
  3     select 1, 'BT'    ,  10 from dual union all
  4     select 1, 'Cash'  ,  10 from dual union all
  5     select 2, 'BT'    ,  50 from dual union all
  6     select 3, 'Cash'  ,  24 from dual union all
  7     select 3, 'BT'    ,  12 from dual union all
  8     select 4, 'wallet', 100 from dual union all
  9     select 5, 'wallet',  20 from dual union all
 10     select 5, 'wallet', 100 from dual
 11    ),

Query begins here; cnt will be 0 if there's only "wallet" per PID:

 12  temp as
 13    (select pid,
 14       sum(case when pos = 'wallet' then 0 else 1 end) cnt
 15     from test
 16     group by pid
 17    )
 18  select a.pid, a.pos, a.amount,
 19    case when b.cnt = 0 then 1 else 0 end wallet_flag
 20  from test a join temp b on a.pid = b.pid
 21  order by a.pid;

       PID POS        AMOUNT WALLET_FLAG
---------- ------ ---------- -----------
         1 wallet         10           0
         1 BT             10           0
         1 Cash           10           0
         2 BT             50           0
         3 Cash           24           0
         3 BT             12           0
         4 wallet        100           1
         5 wallet         20           1
         5 wallet        100           1

9 rows selected.

SQL>

CodePudding user response：

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