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

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

CodePudding user response：

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');

CodePudding user response：

Another option is to use window functions. I would only advise this if you have no indexing on the base table, as the subquery version provided by @nbk will probably be faster otherwise.

SELECT
  COUNT(*) MaleBelowAvgFemaleWeight
FROM (
    SELECT *,
      AVG(CASE WHEN gender = 'F' THEN patient_weight END) OVER () AS AvgFemaleWeight
    FROM patients p
) p
WHERE gender = 'M' AND patient_weight < AvgFemaleWeight;