3.5 — Smart Categorization with CASE

What You'll Learn

• How to use CASE to create categories in SQL
• How this compares to Excel’s IF and IFS functions

From Excel to SQL: Categorizing Data

In Excel, you use IF or IFS to create new categories. In SQL, use CASE.

SQL:

SELECT Amount,
       CASE WHEN Amount > 100 THEN 'Large order'
            ELSE 'Small order' END AS Order_Size
FROM sales;

• CASE WHEN ... THEN ... ELSE ... END allows to check the conditions.
• AS Amount_Category names the new column.

More Examples

Multiple conditions:

SELECT Amount,
       CASE WHEN Amount > 200 THEN 'Very large'
            WHEN Amount > 100 THEN 'Large'
            ELSE 'Small' END AS Order_Size
FROM sales;

Handling NULL Values with CASE

You can use CASE to handle missing (NULL) values and provide defaults, just like using IF(ISBLANK(...)) in Excel.

Example: Default missing Amounts to 0

SELECT Product,
  CASE
    WHEN Amount IS NULL THEN 0
    ELSE Amount
  END AS Amount_Filled
FROM sales;

• If Amount is NULL, the result will be 0; otherwise, it shows the original value.

Tip: You can also use the COALESCE() function to provide a default value for NULL:

SELECT Product, COALESCE(Amount, 0) AS Amount_Filled
FROM sales;

Example: Categorize NULL Amounts as "Unknown"

SELECT Product, Amount,
  CASE
    WHEN Amount IS NULL THEN 'Unknown'
    WHEN Amount >= 100 THEN 'High'
    WHEN Amount >= 50 THEN 'Medium'
    ELSE 'Low'
  END AS Amount_Category
FROM sales;

• If Amount is missing, the category will be "Unknown".

Key Points

• CASE is like IF/IFS in Excel.
• You can create new categories or flags based on your data.

Next Steps

In the next module, you’ll learn how to summarize and group your data—just like using PivotTables in Excel.