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

MysqlSqlDatabase

Mysql Problem Overview

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?

Mysql Solutions

Solution 1 - Mysql

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.

Solution 2 - Mysql

Use a case statement:

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

Solution 3 - Mysql

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;

Solution 4 - Mysql

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

Solution 5 - Mysql

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

Solution 6 - Mysql

SELECT id, amount
FROM report
WHERE type='P'

UNION

SELECT id, (amount * -1) AS amount
FROM report
WHERE type = 'N'

ORDER BY id;

Solution 7 - Mysql

You can try this also

 SELECT id , IF(type='p', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount FROM table
Categories
Recommended Mysql Solutions
Recommended Sql Solutions
Recommended Database Solutions

Previous Solution:

jQuery Ajax POST example with PHP

JavascriptJqueryAjaxPost

Next Solution:

How to apply multiple transforms in CSS?

CssCss Transforms

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionMichaelView Question on Stackoverflow
Solution 1 - MysqlFelipe BuccioniView Answer on Stackoverflow
Solution 2 - MysqlmellamokbView Answer on Stackoverflow
Solution 3 - Mysqluser1210826View Answer on Stackoverflow
Solution 4 - Mysqlsang kaulView Answer on Stackoverflow
Solution 5 - MysqlaWebDeveloperView Answer on Stackoverflow
Solution 6 - MysqllinituxView Answer on Stackoverflow
Solution 7 - MysqlBasant RulesView Answer on Stackoverflow