'sql self join on subquery

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?

sql


Solution 1:[1]

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

Solution 2:[2]

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

Solution 3:[3]

In case of SQL Server or Oracle we can use IIF. In case of MySql use replace IIF with IF.

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

Alternatively we can use CASE WHEN instead of IIF which is supported by all dbms. Here is query using CASE WHEN.

SELECT province_name, SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS freq
FROM provinces
INNER JOIN patients
ON provinces.province_id = patients.province_id
GROUP BY province_name
HAVING SUM(CASE WHEN gender = 'F' THEN 1 ELSE -1 END) > 0

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 Saeed Esmaeelinejad
Solution 3

Related Questions