CODEWITHSUNDEEP
mysqlsqlcase
T. Karter
T. Karter

Reputation: 738

Select field based on value in MySQL

There is mysql table foo:

a | b | c
----------
1 | 2 | 0
2 | 3 | 1
3 | 4 | 0

and a query

SELECT a-b as subA, b-a as subB FROM foo;

subA | subB
-----------
 -1  |   1
 -1  |   1
 -1  |   1

How can I select a-b as sum if c = 0 and b-a as sum if c = 1, so that I have this result:

sum
---
-1
 1
-1

Upvotes: 1

Views: 184

Answers (3)

Akina
Akina

Reputation: 42651

SELECT (a-b)*(1-2*c) AS `sum`
FROM foo

Upvotes: 3

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

This is pretty simple (look in SQLize.online):

SELECT 
    a-b as subA, 
    b-a as subB,
    CASE 
        WHEN c = 0 THEN a-b
        WHEN c = 1 THEN b-a
    END as sum
FROM foo;

Result:

+======+======+=====+
| subA | subB | sum |
+======+======+=====+
| -1   | 1    | -1  |
+------+------+-----+
| -1   | 1    | 1   |
+------+------+-----+
| -1   | 1    | -1  |
+------+------+-----+

Upvotes: 1

GMB
GMB

Reputation: 222482

You can use a case expression:

select f.*,
    case c 
        when 0 then a - b 
        when 1 then b - a
    end as res
from foo f

If c is always 0 or 1, we can get a little fancy with sign():

select f.*, sign(c - 0.5) * (b - a) as res 
from foo f

Upvotes: 1

Related Questions