How to CASE WHEN in ClickHouse

Galaxy Glossary

How do I use CASE WHEN in ClickHouse?

CASE WHEN lets you create conditional columns or transform values on-the-fly during SELECT queries.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What problem does CASE WHEN solve?

CASE WHEN replaces nested IFs or multi-step data cleaning by letting you branch logic inside one SELECT. It is perfect for bucketing order totals, flagging low stock products, or anonymizing customer emails without extra tables.

How do I write a simple CASE WHEN?

Start with CASE, add one or more WHEN condition THEN result pairs, finish with optional ELSE default, and close with END. The expression returns the first matching result.

Example: flag high-value orders

SELECT id, total_amount, CASE WHEN total_amount > 500 THEN 'VIP' WHEN total_amount > 100 THEN 'Preferred' ELSE 'Standard' END AS customer_tier FROM Orders;

Can I nest CASE WHEN blocks?

Yes. Keep nesting shallow for readability. Break into CTEs when logic exceeds 2-3 levels.

How do I combine CASE WHEN with aggregates?

Wrap aggregates inside the CASE expression or vice-versa. Use it to sum only certain rows or create grouped labels.

Example: sum VIP revenue

SELECT SUM(CASE WHEN total_amount > 500 THEN total_amount ELSE 0 END) AS vip_revenue FROM Orders;

Best practices for CASE WHEN

Order conditions from most-specific to least-specific. Avoid overlapping ranges. Alias the result for clarity. Test with small result sets before adding to production queries.

Why How to CASE WHEN in ClickHouse is important

How to CASE WHEN in ClickHouse Example Usage


-- Bucket customers by signup year and flag email domain
SELECT id,
       name,
       CASE WHEN created_at < toDate('2022-01-01') THEN 'Legacy'
            WHEN created_at < toDate('2023-01-01') THEN '2022 Cohort'
            ELSE '2023+' END AS signup_bucket,
       CASE WHEN email ILIKE '%@gmail.com' THEN 'Gmail'
            WHEN email ILIKE '%@yahoo.com' THEN 'Yahoo'
            ELSE 'Other' END AS email_provider
FROM Customers;

How to CASE WHEN in ClickHouse Syntax


CASE WHEN Syntax in ClickHouse:
SELECT
    CASE
        WHEN <condition_1> THEN <result_1>
        [WHEN <condition_N> THEN <result_N> ...]
        [ELSE <default_result>]
    END AS <alias>
FROM <table>;

-- Example with ecommerce tables
SELECT id,
       CASE WHEN stock = 0 THEN 'Out of stock'
            WHEN stock < 10 THEN 'Low'
            ELSE 'OK' END AS stock_status
FROM Products;

Common Mistakes

Using overlapping conditions (e.g., "WHEN total_amount > 100" before "WHEN total_amount > 500"). Fix by ordering from most-specific to least-specific or adding mutually exclusive ranges.
Returning mixed data types (e.g., integer in one branch, string in another). Ensure all THEN/ELSE branches return the same type or use explicit casts.