Coginiti menu Coginiti menu

How to Write a Case Statement

When you write a CASE statement in SQL, you define a specific condition or set of conditions to evaluate and specify the resulting output or action for each possible condition. The statement then evaluates the condition and executes the corresponding action based on the result.

The basic syntax for a CASE statement is as follows:

	WHEN value_1 THEN result_1 
	WHEN value_2 THEN result_2 
	ELSE default_result 

For example, if you want to return “Yes” if the column score is greater than 80 and “No” otherwise, you can use the following CASE statement:

	WHEN score > 80 THEN 'Large'
	WHEN score > 60 THEN 'Medium' 
	ELSE 'Small' 
  END AS result 
FROM your_table;

In this example, the CASE statement checks if the value of the score column is greater than 80. If it is, it returns “Large”. Otherwise, it evaluates the next condition (score > 60) and if so, returns ‘Medium’.  If none of the WHEN conditions are true, the ELSE value off “Small” is returned.

When all cases test for equality, an more compact CASE syntax can be used:

  CASE day_of_week
	WHEN 'Saturday' THEN 'Weekend'
	WHEN 'Sunday' THEN 'Day of rest'
	ELSE 'Work day' 
  END AS day_type 
FROM your_table;