Simple CASE WHEN
Searched CASE WHEN
CASE WHEN with aggregates
CASE WHEN with GROUP BY
Instruction
Okay. In the previous example, we checked the values of a numerical column and produced string values as a result, but you can check a string column and produce numbers just as well. Check it out:
SELECT
first_name,
last_name,
CASE preferred_contact
WHEN 'mobile' THEN 5
WHEN 'e-mail' THEN 0
WHEN 'mail' THEN 10
ELSE 5
END AS contact_cost
FROM candidate;
In the above example, we want to calculate the cost of contacting a given candidate. Based on the values in the column preferred_contact, we provide some numerical values. Please note how we renamed the column in question with AS contact_cost, put after the END keyword.
Exercise
For each application, show the candidate ID (as candidate_id) and the course_id along with the following information: if the status is accepted, show 1. Otherwise, show 0. Name the last column accepted.
Stuck? Here's a hint!
Use:
CASE status WHEN 'accepted' THEN 1 ELSE 0 END
