773
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

8 Answers 8

1139
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.

6
  • 5
    I wonder if there is any advantage to using this IFNULL instead of COALESCE here?
    – Chris
    Commented Aug 31, 2013 at 18:29
  • 4
    From mysql source, I notice 2 definitions of coalesce, one with 2 arguments, and other with a list of arguments, but ifnull invokes the coalesce with 2 parameters sql/item_cmpfunc.h 722: Item_func_ifnull(Item *a, Item *b) :Item_func_coalesce(a,b) {} Commented Sep 1, 2013 at 3:33
  • 2
    The answer is not correct if there are different report types than 'N' and 'P', see BadHorsie's comment in the better "case statement" solution.
    – Trygve
    Commented Jan 20, 2017 at 9:41
  • 5
    @Trygve The question is for 2 conditions, and looking for an IF statement, what's wrong? Commented Jan 31, 2017 at 20:57
  • 2
    @Felipe, the answer is not necessarily 100% correct, there could be other report types than N and P. In your case, this could lead to an error, selecting -amount if report type (as an example) is 'E'. The question fails to mention if there are other report types though, so I remove my downvote. I just like to program defensively in these cases, so a heads up to other readers.
    – Trygve
    Commented Feb 27, 2017 at 12:41
283

Use a case statement:

select id,
    case report.type
        when 'P' then amount
        when 'N' then -amount
    end as amount
from
    `report`
3
  • 5
    @Evan: True. I use them for clarity. Not that it affects anything anyway.
    – mellamokb
    Commented May 10, 2011 at 14:21
  • 3
    I prefer ANSI standard syntax over custom syntax for a particular database. Commented May 3, 2014 at 18:45
  • 2
    This is the best solution because the accepted answer solution isn't necessarily appropriate if there are other values for report.type, or if a new report.type is introduced at a later date. It's saying if report.type = 'P' use amount, otherwise use -amount for anything else. it won't consider the type if it's not 'P'.
    – BadHorsie
    Commented Jan 20, 2015 at 13:41
114
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;
1
  • Answer is unrelated to question! Commented Aug 24, 2022 at 1:33
49
select 
  id,
  case 
    when report_type = 'P' 
    then amount 
    when report_type = 'N' 
    then -amount 
    else null 
  end
from table
0
15

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
14
SELECT id, amount
FROM report
WHERE type='P'

UNION

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

ORDER BY id;
1
  • As the result sets are mutually exclusive, I prefer UNION ALL here.
    – Arth
    Commented May 6, 2016 at 10:47
6

You can try this also

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

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.