Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

'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?

like image 588
Michael Avatar asked May 10 '11 13:05

Michael


People also ask

Can we use if condition in MySQL query?

The MySQL IF() function is used for validating a condition. The IF() function returns a value if the condition is TRUE and another value if the condition is FALSE. The MySQL IF() function can return values that can be either numeric or strings depending upon the context in which the function is used.

What is the use of SELECT clause in SELECT from WHERE statement?

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.


2 Answers

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

See http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html.

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.

like image 81
Felipe Buccioni Avatar answered Sep 19 '22 23:09

Felipe Buccioni


Use a case statement:

select id,     case report.type         when 'P' then amount         when 'N' then -amount     end as amount from     `report` 
like image 44
mellamokb Avatar answered Sep 20 '22 23:09

mellamokb