Using SQLite CASE WHEN with Syntax and Examples

SQLite CASE WHEN

The CASE statement in SQLite is a conditional expression that allows you to execute different actions based on specified conditions. It is often used in SELECT queries to compute conditional values, apply transformations, or group data dynamically based on conditions.

Syntax

CASE  
    WHEN condition1 THEN result1  
    WHEN condition2 THEN result2  
    ...  
    ELSE default_result  
END  

Explanation:

• WHEN: Specifies a condition to evaluate.
• THEN: Specifies the result if the condition evaluates to TRUE.
• ELSE: Specifies a default result if none of the conditions are met (optional).
• END: Marks the end of the CASE expression.

The CASE statement can be used in SELECT, UPDATE, INSERT, or ORDER BY clauses.

Example

Scenario: Categorize employees based on their salary into different salary groups (Low, Medium, or High).

Step 1: Create an "employees" table to store employee data

Code:

CREATE TABLE employees (  
    id INTEGER PRIMARY KEY,      -- Unique identifier for each employee  
    name TEXT,                   -- Employee name  
    salary INTEGER               -- Employee salary  
);  

Step 2: Insert sample data into the "employees" table

Code:

INSERT INTO employees (name, salary) VALUES ('Sara', 45000);  
INSERT INTO employees (name, salary) VALUES ('Bob', 75000);  
INSERT INTO employees (name, salary) VALUES ('Charlie', 120000);  

Code:

-- Categorize employees into salary groups based on their salary  
SELECT  
    id,                            -- Employee ID  
    name,                          -- Employee Name  
    salary,                        -- Employee Salary  
    CASE  
        WHEN salary < 50000 THEN 'Low'         
        -- Salary below 50,000 is "Low"  
        WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'  
        -- Salary between 50,000 and 100,000 is "Medium"  
        ELSE 'High'               
        -- Salary above 100,000 is "High"  
    END AS salary_group            
    -- Create a new column "salary_group"  
FROM employees;  

Output:

-- id  | name      | salary   | salary_group  
-- ----|-----------|----------|--------------  
-- 1   | Sara      | 45000    | Low  
-- 2   | Bob       | 75000    | Medium  
-- 3   | Charlie   | 120000   | High  

Step 4: Use the CASE statement in an UPDATE query

Code:

-- Add a bonus to employees based on their salary group  
ALTER TABLE employees ADD COLUMN bonus INTEGER; -- Add a new "bonus" column  

UPDATE employees  
SET bonus = CASE  
    WHEN salary < 50000 THEN 5000        
    -- Bonus for "Low" salary group  
    WHEN salary BETWEEN 50000 AND 100000 THEN 3000 
    -- Bonus for "Medium" salary group  
    ELSE 1000                           
    -- Bonus for "High" salary group  
END;  

Step 5: Verify the updates to the "employees" table

Code:

SELECT * FROM employees;  

Output:

 
-- id  | name      | salary   | bonus  
-- ----|-----------|----------|------  
-- 1   | Sara      | 45000    | 5000  
-- 2   | Bob       | 75000    | 3000  
-- 3   | Charlie   | 120000   | 1000 

Explanation:

1. Creating the Table:

• The employees table is created with three columns: id, name, and salary.

2. Inserting Data:

• Sample employee data is inserted into the table using the INSERT statement.

3. Using CASE in SELECT:

• The CASE statement is used in the SELECT query to evaluate salary ranges and assign labels (Low, Medium, High) to a new column (salary_group).
• The conditions are evaluated sequentially, and the first TRUE condition determines the result.

4. Adding a New Column:

• The ALTER TABLE statement adds a new column (bonus) to the employees table to store bonus amounts.

5. Using CASE in UPDATE:

• The CASE statement is used in the UPDATE query to assign bonuses based on salary ranges.

6. Verifying Results:

• The final SELECT query retrieves all columns, confirming the updates and salary group categorization.

Additional Information

• The CASE statement is particularly useful for:
• Dynamic grouping or categorization.
• Applying conditional logic within queries.
• Simplifying complex transformations.
• It can be nested to handle more complex conditions, but this may affect readability.

Practical Guides to SQLite Snippets and Examples.