'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report 

I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

7 Answers

SELECT id, IF(type = 'P', amount, amount * -1) as amount FROM report 

See .

Additionally, you could handle when the condition is null. In the case of a null amount:

SELECT id, IF(type = 'P', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount FROM report 

The part IFNULL(amount,0) means when amount is not null return amount else return 0.

Use a case statement:

select id, case report.type when 'P' then amount when 'N' then -amount end as amount from `report` 

SELECT CompanyName, CASE WHEN Country IN ('USA', 'Canada') THEN 'North America' WHEN Country = 'Brazil' THEN 'South America' ELSE 'Europe' END AS Continent FROM Suppliers ORDER BY CompanyName; 

select id, case when report_type = 'P' then amount when report_type = 'N' then -amount else null end from table 

Most simplest way is to use a IF(). Yes Mysql allows you to do conditional logic. IF function takes 3 params CONDITION, TRUE OUTCOME, FALSE OUTCOME.

So Logic is

if report.type = 'p' amount = amount else amount = -1*amount 

SQL

SELECT id, IF(report.type = 'P', abs(amount), -1*abs(amount)) as amount FROM report 

You may skip abs() if all no's are +ve only

SELECT id, amount FROM report WHERE type='P' UNION SELECT id, (amount * -1) AS amount FROM report WHERE type = 'N' ORDER BY id; 

You can try this also

 SELECT id , IF(type='p', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount FROM table