CASE METHOD

The CASE method is a powerful conditional operator that is used for evaluating multiple conditions against a field/column and returns specific values based on the evaluated condition. The general syntax for the CASE methods looks like the following:

CASE
    WHEN some_condition THEN some_value
    WHEN some_condition THEN some_value
    ELSE default_value
END AS Alias_Name

Let's see this in action

Using CASE Method

To demonstrate the use of CASE method we will classify products into `BUY`, `HOLD` and `SELL` depending on the category they belong to. We will assume the following:

Smartphones, Office, Monitors: Hold
Storage, Gaming, Camera: Buy
Everything Else: Sell

The implemetation will look like the following

 SELECT 
    product_name,
    category,
    price,
    CASE 
        WHEN category IN ('Smartphones', 'Office', 'Monitors') THEN 'Hold'
        WHEN category IN ('Storage', 'Gaming', 'Camera') THEN 'Buy'
        ELSE 'Sell'
    END AS status
from products
limit = 10;

Notice that the results in the output now map the column sector to the respective classification values specified in the CASE method.

Another example we can look at is using case to evaluate numeric values. Let's perform the same CASE method above against the price column.

SELECT product_name,
    category,
    price,
CASE
    WHEN price >= 700 THEN 'Sell'
    WHEN price BETWEEN 400 AND 699 THEN 'Hold'
    ELSE 'Buy'
END AS status
FROM products
limit 15;

On this case again, we see the case method providing a multi-level comparision logic for data filtering.

Beyond simple classification, CASE method can be used for computation as well. We will look at this further in a separate section