sql self join on subquery-CodePudding

So I have a query to implement as:

Show the province that has more patients identified as 'M' than 'F'. Must only show full province_name

provinces table has province_id, province_name, while patients has gender, province_id and other attributes.

My approach is to first determine frequency of each gender for provinces:

SELECT * FROM
(
  SELECT province_name, gender, count(gender) as freq from provinces
  INNER JOIN patients
  ON provinces.province_id = patients.province_id
  GROUP BY province_name, gender
)
as province_gender_freq

Now I plan to make a self join on province_gender_freq with join conditions as needed.

Is it optimum way of achieving it?

CodePudding user response：

Use conditional aggregation to compare to compare the count of male patients against the count of female patients:

SELECT pr.province_name
FROM provinces pr
INNER JOIN patients pa
    ON pa.province_id = pr.province_id
GROUP BY pr.province_name
HAVING COUNT(CASE WHEN pa.gender = 'M' THEN 1 END) >
       COUNT(CASE WHEN pa.gender = 'F' THEN 1 END);

CodePudding user response：

Try this by using Sum and also you can get how many M is more then F:

SELECT * FROM
(
  SELECT province_name, gender, count(gender) as freq, SUM(CASE WHEN gender='M' THEN 1 ELSE -1 END) SumGender
   from provinces
  INNER JOIN patients
  ON provinces.province_id = patients.province_id
  GROUP BY province_name, gender
)
as province_gender_freq
WHERE province_gender_freq.SumGender>=1

CodePudding user response：

You can use HAVING and add condition as SUM(IIF(gender='F', 1, -1)) > 0. And update your select statement to get correct frequency for 'F' use SUM(IIF(gender='F', 1, 0)) as freq. Use GROUPBY with only province_name.

SELECT province_name, SUM(IIF(gender='F', 1, 0)) as freq
FROM provinces
INNER JOIN patients
ON provinces.province_id = patients.province_id
GROUP BY province_name
HAVING SUM(IIF(gender='F', 1, -1)) > 0