How to use conditional statement using CASE

Let's say we have a table that gives information about employees

We want to categorize employees based on hire_date.

Anyone who was hired before 2006-01-01 is Super skilled Employee

Anyone hired between 2006-01-01 and 2012-01-01 is Skilled Employee

Anyone hired after 2012-01-01 is New Employee

Our task is to count them based on hire date and provide results in 3 columns.

Something like this...

We can accomplish this by using CASE statement.

Let's try to derive the first column and see if it comes out as expected.

We are using output as 1 and 0 so we can use SUM function to get the total for the category super_skilled

This looks fine so we can now go ahead and add other 2 categories and get the desired result.

The end result count should total to 1000 as in our table we have 1000 records of students.

226+394+380 = 1000

This confirms that result is correct.