How do I separate two genders from the same column and use them separately?

October 23, 2022

I have a table where one column has the genders (M,F,O) and I have another column for patient_weight, I need to get the average weight of the Females and then show a count of all the males that weigh less than the average female. Here is what I have so far:

SELECT top 50 * 
FROM 
    dbo.patients

SELECT
    AVG(patient_weight)
FROM 
    patients
WHERE gender LIKE 'F';

SELECT
COUNT
    (patient_weight)
FROM 
    patients
WHERE gender LIKE 'M' AND patient_weight < 77

>Solution :

As the query with the female average weight will return a scalar value. you can use it directy as criteria for the weight of the male patients

SELECT
COUNT(*) number_of_malepatients_below_avg
FROM 
    patients
WHERE gender LIKE 'M' AND patient_weight < (SELECT
    AVG(patient_weight)
FROM 
    patients
WHERE gender LIKE 'F');