# case {#case}

The `case` expression goes through conditions and returns a value when the first condition is met \(like an if-else statement\).

The `case` expression must contain at least one when...then... expression. Once a `when` condition is true, it will stop reading and return the result specified in the `then` clause. If no conditions are true, it returns the value in the `else` clause. If there is no `else` part and no conditions are true, it returns NULL. The return type of all conditions in a `case` expression must be the same.

The `case...when...` expression can be used in distributed queries.

**Parent topic:**[SQL Reference](../../Programming/SQLStatements/SQLStatement.md)

## Syntax {#syntax}

``` {#codeblock_yxy_5hn_ryb}
case
    when condition_1 then expression_1
    [when condition_2 then expression_2]
    ...
    [when condition_n then expression_n]
    [else expression_end]
end
```

Starting from version 1.30.222.00.10, the when and then clauses of a CASE expression no longer need to be written on a single line.

## Examples {#examples}

``` {#codeblock_ayy_5hn_ryb}
t = table(`st0`st1`st2`st4`st5`st6 as sym, 80 200 150 220 130 190 as vol)
select sym, case 
	when vol < 100 then -1 
	when vol < 200 then 0
	else 1
end as flag
from t
```

|sym|flag|
|---|----|
|st0|-1|
|st1|1|
|st2|0|
|st4|1|
|st5|0|
|st6|0|

``` {#codeblock_cyy_5hn_ryb}
t = table(2022.01.01 2022.01.01 2022.01.01 2022.01.01 2022.01.01 2022.01.01 as date, `a`a`b`b`a`b as id, 300 290 302 296 304 320 as val)
select date, case when t.id == `a then val end as `GroupA, case when t.id == `b then val end as `GroupB from t
```

|date|GroupA|GroupB|
|----|------|------|
|2022.01.01|300||
|2022.01.01|290||
|2022.01.01||302|
|2022.01.01||296|
|2022.01.01|304||
|2022.01.01||320|

``` {#codeblock_eyy_5hn_ryb}
select sum(val) as total from t group by case when t.val < 300 then 0 else 1 end as flag
```

|flag|total|
|----|-----|
|1|1,226|
|0|586|

Use analytic functions in a `case` expression.

``` {#codeblock_e4f_1mt_xbc}
t = table([2023.06M,2023.07M,2023.08M,2023.07M,2023.07M,2023.06M,2023.08M,2023.06M,2023.08M] as month, ["A","A","B","B","C","C","A","B","C"] as flag, [1200,1500,1200,1300,1400,1400,1400,1000,1300] as val)
t
```

<table id="table_cgk_2lf_xbc"><thead><tr><th align="left">

month

</th><th align="left">

flag

</th><th align="left">

val

</th></tr></thead><tbody><tr><td align="left">

2023.06M

</td><td align="left">

A

</td><td align="left">

1,200

</td></tr><tr><td align="left">

2023.07M

</td><td align="left">

A

</td><td align="left">

1,500

</td></tr><tr><td align="left">

2023.08M

</td><td align="left">

B

</td><td align="left">

1,200

</td></tr><tr><td align="left">

2023.07M

</td><td align="left">

B

</td><td align="left">

1,300

</td></tr><tr><td align="left">

2023.07M

</td><td align="left">

C

</td><td align="left">

1,400

</td></tr><tr><td align="left">

2023.06M

</td><td align="left">

C

</td><td align="left">

1,400

</td></tr><tr><td align="left">

2023.08M

</td><td align="left">

A

</td><td align="left">

1,400

</td></tr><tr><td align="left">

2023.06M

</td><td align="left">

B

</td><td align="left">

1,000

</td></tr><tr><td align="left">

2023.08M

</td><td align="left">

C

</td><td align="left">

1,300

</td></tr></tbody>
</table>For flag "A", calculate the value deviation from monthly average. For other flags, calculate the standard deviation of value for the month.

``` {#codeblock_bqp_glf_xbc}
select month,flag,(case when flag = "A"
then (val - avg(val)over(partition by month))
else std(val)over(partition by month) end) as value
from t
```

<table id="table_sbh_hlf_xbc"><thead><tr><th align="left">

month

</th><th align="left">

flag

</th><th align="left">

value

</th></tr></thead><tbody><tr><td align="left">

2023.06M

</td><td align="left">

A

</td><td align="left">

0

</td></tr><tr><td align="left">

2023.07M

</td><td align="left">

A

</td><td align="left">

100

</td></tr><tr><td align="left">

2023.08M

</td><td align="left">

B

</td><td align="left">

100

</td></tr><tr><td align="left">

2023.07M

</td><td align="left">

B

</td><td align="left">

100

</td></tr><tr><td align="left">

2023.07M

</td><td align="left">

C

</td><td align="left">

100

</td></tr><tr><td align="left">

2023.06M

</td><td align="left">

C

</td><td align="left">

200

</td></tr><tr><td align="left">

2023.08M

</td><td align="left">

A

</td><td align="left">

100

</td></tr><tr><td align="left">

2023.06M

</td><td align="left">

B

</td><td align="left">

200

</td></tr><tr><td align="left">

2023.08M

</td><td align="left">

C

</td><td align="left">

100

</td></tr></tbody>
</table>