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;

     product_name     |  category   |  price  | status
----------------------+-------------+---------+--------
 Samsung Galaxy S23   | Smartphones |  799.99 | Hold
 MacBook Pro 16"      | Laptops     | 2499.99 | Sell
 Dell XPS 13          | Laptops     | 1299.99 | Sell
 iPad Air             | Tablets     |  599.99 | Sell
 AirPods Pro          | Audio       |  249.99 | Sell
 Sony WH-1000XM4      | Audio       |  349.99 | Sell
 Nintendo Switch      | Gaming      |  299.99 | Buy
 PlayStation 5        | Gaming      |  499.99 | Buy
 Xbox Series X        | Gaming      |  499.99 | Buy
 Apple Watch Series 8 | Wearables   |  399.99 | Sell
(10 rows)

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;

     product_name     |  category   |  price  | status
----------------------+-------------+---------+--------
 Samsung Galaxy S23   | Smartphones |  799.99 | Sell
 MacBook Pro 16"      | Laptops     | 2499.99 | Sell
 Dell XPS 13          | Laptops     | 1299.99 | Sell
 iPad Air             | Tablets     |  599.99 | Hold
 AirPods Pro          | Audio       |  249.99 | Buy
 Sony WH-1000XM4      | Audio       |  349.99 | Buy
 Nintendo Switch      | Gaming      |  299.99 | Buy
 PlayStation 5        | Gaming      |  499.99 | Hold
 Xbox Series X        | Gaming      |  499.99 | Hold
 Apple Watch Series 8 | Wearables   |  399.99 | Buy
 Samsung 65" QLED TV  | TV          | 1799.99 | Sell
 LG OLED 55"          | TV          | 1299.99 | Sell
 Canon EOS R5         | Camera      | 3899.99 | Sell
 GoPro Hero 11        | Camera      |  399.99 | Buy
 DJI Mini 3           | Drones      |  669.99 | Hold
(15 rows)

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