Ken Ken - 4 months ago 29
SQL Question

How to use Mysql SELECT CASE AS Syntax

I am trying to use a select case statement on mysql DB.

SELECT
t.name, t.colour,
CASE WHEN (Amount < 0) AS small, CASE WHEN (Amount > 0) AS large FROM t


Error:
......for the right syntax to use near 'AS small, CASE (Amount > 0)

I want my output like so

Name Colour Small Large
item1 red -35
item2 blue 48
etc.


Been trying with no luck. Help please.

Answer

Here it is:

SELECT 
 t.name, 
 t.colour,
 CASE WHEN (t.Amount < 0) THEN t.Amount END AS small,  
 CASE WHEN (t.Amount > 0) THEN t.Amount END AS large 
FROM t

Understanding CASE WHEN:

Example #1:

Suppose,

IF n < 0 THEN
    'N is negative'
else if n == 0 THEN 'N is Zero'
else 'N is positive'

Let's convert this if-else if chain in MySQL using CASE WHEN expression:

SET @n := -9;
SELECT
CASE WHEN @n <0 THEN 'N is Negative'
     WHEN @n =0 THEN 'N is 0'
     ELSE 'N is positive' END AS output;

Output:

    output
N is Negative

Example #2:

Now we want to convert the following three if statements in MySQL

IF n <0 THEN 'N is Negative'
IF n == 0 THEN 'N is Zero'
IF n > 0 THEN 'N is Positive'

SET @n := 5;
SELECT
    CASE WHEN @n <0 THEN 'N is Negative' END AS negativeOutput,
    CASE WHEN @n =0 THEN 'N is 0' END AS zeroOutput,
    CASE WHEN @n > 0 THEN 'N is positive' END AS positiveOutput;

Output:

Now the output has three columns:

negativeOutput     zeroOutput     positiveOutput
                                   N is positive
Comments