day 40 | 50 Days of SQL | SELECT, CASE WHEN THEN END AS, FROM, SUM(), GROUP BY | SQL Query

Description:
Calculating Admission Costs Based on Insurance Status in a Hospital Database

In this lab, you will be working with a database containing a table called admissions. Your task is to calculate the total cost of admissions for patients based on their insurance status.

Each admission costs $50 for patients without insurance and $10 for patients with insurance. You can determine if a patient has insurance based on their patient_id; all patients with an even patient_id have insurance.

You are required to write a SQL query that does two things:

Label each patient with 'Yes' if they have insurance and 'No' if they don't.
Calculate the total admission cost for each group ('Yes' and 'No').
Concepts Required
Conditional Logic: Use of the CASE statement to apply conditions.
Aggregation: Using SUM to calculate total costs.
Grouping Records: Usage of GROUP BY to segregate data into groups.
Your output should include two columns: one that states if the patient has insurance ('Yes' or 'No') and another that gives the total admission cost for that group.
Query:
select
case
when patient_id%2=0 then 'Yes'
else 'No'
end
as 'has_insurance',sum(case
when patient_id%2=0 then 10
else 50
end)
as cost_after_insurance from admissions group by
has_insurance;
#codedamn #challenge #solution #sql #50dayschallenge