# CASE

> Describes the `CASE` statement used for conditional logic within queries, including syntax and examples for usage in `SELECT`, `WHERE`, `GROUP BY`, and `ORDER BY` clauses.

# CASE

The `CASE` expression evaluates conditions in order and returns the result for the first condition that is true. If no condition is true, it returns the `ELSE` result, or `NULL` when `ELSE` is omitted.

## Syntax

```sql
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result
END
```

- `condition1`, `condition2`, ...: The conditions to evaluate against the expression.
- `result1`, `result2`, ...: The values to return when the corresponding condition is met.
- `result`: The value to return when none of the conditions are met (optional).

## Examples

The `CASE` statement can be used in various clauses, such as `SELECT`, `WHERE`, `ORDER BY` and `GROUP BY`.

### Use `CASE` in `SELECT`

In the `SELECT` clause, you can use the `CASE` statement to create new columns based on conditions.
please see [the example](/user-guide/query-data/sql.md#case-expression) in the query data guide.

You can also use `CASE` with functions like `SUM` to conditionally aggregate data.
for example, you can calculate the total number of logs with status 200 and 404:

```sql
SELECT
    SUM(CASE WHEN status_code = '200' THEN 1 ELSE 0 END) AS status_200_count,
    SUM(CASE WHEN status_code = '404' THEN 1 ELSE 0 END) AS status_404_count
FROM nginx_logs;
```

### Use `CASE` in `WHERE`

In the `WHERE` clause, you can filter rows based on conditions.
For example, the following query retrieves data from the `monitor` table based on the `ts` condition:

```sql
SELECT * 
FROM monitor 
WHERE host = CASE 
                  WHEN ts > '2023-12-13 02:05:46' THEN '127.0.0.1' 
                  ELSE '127.0.0.2' 
              END;
```

### Use `CASE` in `GROUP BY`

The `CASE` statement can be utilized in the `GROUP BY` clause to categorize data based on specific conditions. For instance, the following query groups data by the `host` column and classifies the `cpu` column into three categories: 'high', 'medium', and 'low':

```sql
SELECT
    host,
    COUNT(*) AS count,
    CASE
        WHEN cpu > 0.5 THEN 'high'
        WHEN cpu > 0.3 THEN 'medium'
        ELSE 'low'
    END AS cpu_status
FROM monitor
GROUP BY 
    host, cpu_status;
```

### Use `CASE` in `ORDER BY`

The following query sorts rows by `status_code` when it is not `NULL`, and by `http_method` otherwise:

```sql
SELECT *
FROM nginx_logs
ORDER BY
    CASE
        WHEN status_code IS NOT NULL THEN status_code
        ELSE http_method
    END;
```