Comprehensive guide to important Points and tricky conceptual issues in SQL

The CASE statement is one of the most powerful and flexible tools in SQL. It allows conditional logic anywhere in your query—SELECT, WHERE, GROUP BY, ORDER BY, and especially within aggregations.

✅ General Syntax of CASE

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

🔍 Use Cases of CASE in Different Clauses

✅ 1. In SELECT — Conditional column values

SELECT name,
       salary,
       CASE 
         WHEN salary > 100000 THEN 'High'
         WHEN salary > 50000 THEN 'Medium'
         ELSE 'Low'
       END AS salary_band
FROM employees;

📌 Labeling or categorizing data based on conditions.

✅ 2. In WHERE — Conditional filtering (less common)

SELECT * 
FROM orders
WHERE 
  CASE 
    WHEN status = 'pending' THEN amount > 100
    ELSE TRUE
  END;

📌 Used sparingly, but can apply complex filters dynamically.

✅ 3. In ORDER BY — Conditional sort logic

SELECT name, status
FROM tasks
ORDER BY 
  CASE 
    WHEN status = 'urgent' THEN 1
    WHEN status = 'high' THEN 2
    ELSE 3
  END;

📌 Custom ordering of categorical values.

✅ 4. In Aggregation — Conditional counting or summing

a. Count by condition:

SELECT 
  department,
  COUNT(*) AS total_employees,
  COUNT(CASE WHEN gender = 'Male' THEN 1 END) AS male_count,
  COUNT(CASE WHEN gender = 'Female' THEN 1 END) AS female_count
FROM employees
GROUP BY department;

b. Sum conditionally:

SELECT 
  department,
  SUM(CASE WHEN salary > 50000 THEN salary ELSE 0 END) AS high_earner_total
FROM employees
GROUP BY department;

c. Multiple category aggregation:

SELECT 
  product_type,
  SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered_count,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count
FROM orders
GROUP BY product_type;

✅ 5. In GROUP BY — Grouping based on a derived value

SELECT 
  CASE 
    WHEN age < 18 THEN 'Minor'
    WHEN age BETWEEN 18 AND 60 THEN 'Adult'
    ELSE 'Senior'
  END AS age_group,
  COUNT(*) 
FROM users
GROUP BY 
  CASE 
    WHEN age < 18 THEN 'Minor'
    WHEN age BETWEEN 18 AND 60 THEN 'Adult'
    ELSE 'Senior'
  END;

📌 You must repeat the CASE in GROUP BY unless you use a subquery or CTE.

✅ 6. Nested CASE

SELECT name,
  CASE 
    WHEN country = 'India' THEN
      CASE 
        WHEN city = 'Delhi' THEN 'North'
        WHEN city = 'Mumbai' THEN 'West'
        ELSE 'Other'
      END
    ELSE 'International'
  END AS region
FROM users;

📌 Useful for multi-layered logic.

🧠 Summary Table

Pages: 1 2 3