CODEWITHSUNDEEP
mysqlsqldatabase
Michael
Michael

Reputation: 14218

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

Upvotes: 771

Views: 1600456

Answers (8)

rotarydial
rotarydial

Reputation: 2581

This is not fantastic, but I did this IF() nesting to normalize a few log messages that otherwise had unique values that made them not group:

SELECT 
    IF(process_error LIKE 'Already processed message id %', 'already processed',
      IF(process_error LIKE 'Cannot calculate size of %', 'sequence error', process_error)
    ) AS process_err,
    COUNT(id) AS process_count
FROM system_log
WHERE started >= '2023-06-01'
GROUP BY 1
ORDER BY process_count DESC

I wouldn't do this for more than a couple values, but for a throwaway query it's fine. It led to this output:


|process_err                                  |process_count|
|---------------------------------------------|-------------|
|already processed                            |617          |
|Failed to process message: uncaught error    |174          |
|sequence error                               |135          |
|Cannot read property 'FooBar' of undefined   |118          |
|Missing field BarBaz                         |8            |
|Ignoring: no matches found                   |1            |

Whereas without it, rows 1 and 3 would be spread out over hundreds of rows.

Upvotes: 1

Felipe Buccioni
Felipe Buccioni

Reputation: 19668

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

See https://dev.mysql.com/doc/refman/8.0/en/flow-control-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.

Upvotes: 1138

Basant Rules
Basant Rules

Reputation: 807

You can try this also

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

Upvotes: 6

sang kaul
sang kaul

Reputation: 509

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

Upvotes: 49

aWebDeveloper
aWebDeveloper

Reputation: 38332

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

Upvotes: 15

linitux
linitux

Reputation: 391

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

UNION

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

ORDER BY id;

Upvotes: 14

user1210826
user1210826

Reputation: 1152

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;

Upvotes: 114

mellamokb
mellamokb

Reputation: 56769

Use a case statement:

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

Upvotes: 283

Related Questions