CODEWITHSUNDEEP
mysqlsqlcase
Andrey
Andrey

Reputation: 1759

Case with multiple records

I have a data set which looks like this:

id No_trans Bank
1   2       VISA
1   1       PAYPAL
2   3       MASTERCARD
3   1       MASTERCARD
3   4       PAYPAL
4   1       PAYPAL

What I need to do is if id has PAYPAL, change it to another bank, for example. For id = 1, PAYPAL should be as VISA, but for id = 3, it should be MASTERCARD, however, for id = 4, it should be PAYPAL because it doesn't have another Bank record.

select id ,count(No_trans) , 
 case Bank when  PAYPAL then VISA 
 when PAYPAL then MASTERCARD 
 else 
  PAYPAL
 end as bank
from table

Grouping by 1 and 3 is not going to work, because I need to change it depending on which other record has the same id bank.

If this ID has a bank different from PAYPAL, use it for other records with the same ID, if not, use it as it is.

create temporary table t1
(
    ID INT
    , no_trans int
    , bank  varchar (255)

  );

 INSERT INTO t1 values
  (1,   2 ,      'VISA'),
(1 ,  1     ,  'PAYPAL'),
(2,   3     , 'MASTERCARD'),
(3 ,  1      , 'MASTERCARD'),
(3,   4      , 'PAYPAL'),
(4 ,  1      , 'PAYPAL');


select * from t1 
where id = 1

return

 '1','2','VISA'
'1','1','PAYPAL'

I need

 '1','2','VISA'
'1','1','VISA'

for

select * from t1 where id = 3 should be 

ID, no_trans, bank
3, 1, MASTERCARD
3, 4, MASTERCARD

for

select * from t1 where id = 4

ID, no_trans, bank
4, 1, PAYPAL

Upvotes: 0

Views: 82

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You can use aggregation:

select id,
       coalesce(max(case when Bank <> 'PayPal' then Bank end),
                max(Bank)
               )
from t
group by id;

If you want the original rows, then perhaps a subquery is simpler:

select t.*,
       (select t2.bank
        from t t2
        where t2.id = t.id 
        order by (t2.bank <> 'PayPal) desc
        limit 1
       ) as new_bank
from t;

Upvotes: 1

Ankur Patel
Ankur Patel

Reputation: 1433

Try the following:

select 
  ID, 
  no_trans, 
  If (id = 1 and bank = 'PAYPAL', 'VISA',bank)as bank 
from t1 
where id = 1

For Id = 3

select 
  ID, 
  no_trans, 
  If (id = 3 and bank = 'PAYPAL', 'MATERCARD',bank) as bank 
from t1 
where id = 3;

If you need all of them combined:

select 
  ID, 
  no_trans, 
  If (bank = 'PAYPAL',If(id = 1,'VISA',If(id=3,'MASTERCARD',bank)),bank) as bank 
from t1;

SQLFIDDLE DEMO

Upvotes: 1

Related Questions