Not satisfied by the Answer? Still have doubts?
Ask Question

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

SELECT id, amount FROM report

I need add up to be sum if report.type='P' and - sum if report.type='N'. How would I add this to the above question?
database mysql sql

2 Answers


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

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

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

Login / Signup to Answer the Question.

  • Be descriptive with your answer, and try to avoid copy-pasting external links, instead guide the user who has asked the question, where can they find the detailed answer.
  • You cannot share any URL in answer.
  • To make a text bold, surround the text with single asterisk, for example *bold text*
  • To format a text like code, surround the text within double asterisk, for example **code**
  • To add a multiline piece of code, surround the whole code within triple asterisk, for example *** multiline code ***
  • Please verify before submitting the answer.