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

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

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:

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':

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:

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