Ken - 7 months ago 46

SQL Question

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
```