Here is a practical problem you might face while preparing a customer analysis report.
You have a customers table containing customer IDs, names and annual income.
The business team wants to understand how many customers fall into each income category.
The categories are:
- Low Income: Below ₹3,00,000
- Middle Income: ₹3,00,000 to ₹7,00,000
- High Income: Above ₹7,00,000
How would you create this report using SQL?
customers
customer_id | name | annual_income
101 | Rahul | 250000
102 | Priya | 450000
103 | Amit | 850000
104 | Sneha | 300000
105 | Karan | 650000
106 | Neha | 950000
107 | Rohan | 200000
Your Challenge
Write a query to:
- Categorize customers using CASE WHEN.
- Count the number of customers in each category.
- Display the income category along with the total customer count.
- Do not change the original table data.
Think before you scroll!
The SQL Solution (Oracle)
SELECT
CASE
WHEN annual_income < 300000
THEN 'Low Income'
WHEN annual_income <= 700000
THEN 'Middle Income'
ELSE 'High Income'
END AS income_category,
COUNT(*) AS total_customers
FROM customers
GROUP BY
CASE
WHEN annual_income < 300000
THEN 'Low Income'
WHEN annual_income <= 700000
THEN 'Middle Income'
ELSE 'High Income'
END;
Expected Output
income_category | total_customers
Low Income | 2
Middle Income | 3
High Income | 2
Why Does This Work?
CASE WHEN checks the annual income of each customer and assigns an income category.
Customers earning below ₹3,00,000 are placed in Low Income.
Customers earning from ₹3,00,000 to ₹7,00,000 are placed in Middle Income.
All remaining customers are placed in High Income.
GROUP BY combines customers belonging to the same category.
COUNT(*) calculates how many customers belong to each group.
That's it. CASE WHEN and GROUP BY help convert individual customer records into a useful business report.
One Thing to Remember:
The order of conditions in CASE WHEN matters.
Once a condition is satisfied, SQL returns its corresponding result without checking the remaining conditions.
Your Turn
Suppose you want to display the actual customer names along with their income categories instead of just counting customers.
How would you modify this query?
Share your SQL query in the comments!
Top comments (0)