Set a column value to be null based on value from another column

Question

I'm preparing a select query that should set a field as null if certain value is present in another field of the same query.

select statement_id, prod_id, description, exp_type, 
        curr_amt as Digital_income, curr_amt as Physical income 
from table1 
where date(created) = 'somedate'

Here, the field exp_type will have values like "Digital", "Physical", "Download", "Stream".

I'm cloning curr_amt field as digital and physical. Most of the times both will have similar values only.

My requirement is if the exp_type is "Physical" I need the curr_amt as 'Digital_income'` to be null and visa versa.

Can anyone assist me in getting the desired output?

I tried using CASE, ISNULL and COALESCE but in vain

Barmar · Accepted Answer · 2022-12-13 15:50:46Z

1

Use IF().

select statement_id, prod_id, description, exp_type, 
        IF(exp_type = 'Digital', curr_amt, NULL) as Digital_income, 
        IF(exp_type = 'Physical', curr_amt, NULL) as Physical_income 
from table1 
where date(created) = 'somedate'

DEMO

edited Dec 13, 2022 at 15:50

answered Dec 12, 2022 at 14:09

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Subramanian V Over a year ago

It is making both of my columns (Digital_income & Physical_income) empty because "curr_amt" will have either 'Digital' or 'Physical' always and hence both of the columns are returning NULL. Below is the kind of output I'm expecting exp_type Digital Income Physical Income Digital 0.00709363 NULL Physical NULL 0.04552167

Barmar Over a year ago

It shouldn't do that. If should return curr_amt when exp_type matches the IF condition.

Barmar Over a year ago

I've added a link to a demo showing that it works.

Subramanian V Over a year ago

Its weird to be not working for me. Let me try again

Barmar Over a year ago

Maybe there are extra spaces or other characters in exp_type. Can you make a db-fiddle?

Collectives™ on Stack Overflow

Set a column value to be null based on value from another column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related