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?

0

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.

6

Use a case statement:

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

3

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

0

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

0

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

1

You can try this also

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