The SQL CASE expression applies conditional logic inside a query. It can label rows, group numeric values into bands, build conditional aggregates and make reports easier to read. CASE is often clearer than exporting data and categorizing it manually in a spreadsheet.
Basic CASE syntax
A searched CASE evaluates conditions in order and returns the result for the first condition that is true. ELSE is optional, but it is often good practice to include a fallback so unanticipated values are visible.
The example labels exam scores using illustrative cutoffs. Replace them with the grading criteria that actually apply to your class or reporting context.
SELECT student_id, score,
CASE
WHEN score >= 90 THEN 'A range'
WHEN score >= 80 THEN 'B range'
WHEN score >= 70 THEN 'C range'
ELSE 'Below 70'
END AS score_band
FROM ExamResults;Order conditions from specific to general
SQL returns the result for the first matching WHEN condition. If a broad condition appears before a more specific one, the later condition might never be reached. In a numeric score example, checking score >= 70 before score >= 90 would label a score of 95 as C range.
Test boundaries such as 69, 70, 79, 80, 89 and 90 so the categories behave as intended. Also decide how NULL scores should be handled.
Use CASE to create readable report labels
CASE can translate internal status codes into labels for a dashboard, map order values into size bands, or classify dates into reporting categories. Keep the logic close to the definition used by the business or course rather than inventing categories in several separate reports.
If categories change often, a separate mapping table may be easier to maintain than a long CASE statement. The best design depends on how frequently the rules change and how many queries need them.
SELECT order_id, amount,
CASE
WHEN amount >= 1000 THEN 'Large'
WHEN amount >= 250 THEN 'Medium'
WHEN amount >= 0 THEN 'Small'
ELSE 'Check amount'
END AS order_band
FROM Orders;Conditional aggregation with CASE
CASE can be placed inside aggregate expressions to summarize only rows that meet a condition. This technique is useful when one output row needs several metrics, such as counts by status or total revenue from one order type.
Some SQL engines support aggregate FILTER syntax as an alternative; CASE is widely recognized across many common SQL dialects.
SELECT customer_id,
SUM(CASE WHEN status = 'Paid' THEN amount ELSE 0 END) AS paid_total,
SUM(CASE WHEN status = 'Refunded' THEN amount ELSE 0 END) AS refunded_total
FROM Orders
GROUP BY customer_id;Debugging CASE expressions
Check the data type of each THEN and ELSE result. Mixing incompatible data types can cause conversion errors or surprising output. Confirm that string labels match the expected spelling and consider what should happen for NULL, negative or out-of-range values.
Keep long conditional logic readable by formatting each WHEN on its own line and testing the logic on a small sample before using it in a report.
- List the intended categories before writing the expression.
- Check boundary values and NULL handling.
- Keep result types compatible.
- Include a meaningful ELSE where appropriate.
- Use a lookup table when rules change frequently.
Understanding the concept is the first step.
Explore related resources or learn about subject-focused tutoring designed to help you understand the material.