I need a solution for this SQL query problem-CodePudding

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

patient table : patient_id INT first_name TEXT last_name TEXT gender CHAR(1) birth_date DATE city TEXT province_id CHAR(2) allergies TEXT height INT weight INT

Province_names table: province_id CHAR(2) province_name TEXT

I tried this but it's not working.

`select province_name
from patients p,province_names pn
where p.province_id = pn.province_id
and (count(p.province_id) group by p.patient_id having p.gender = 'M' )  >   
(count(p.province_id) group by p.patient_id having p.gender = 'F') `

CodePudding user response：

Aggregation provides one straightforward approach:

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

CodePudding user response：

I think your problem is solved by this query

    select res.province_name

from (SELECT pn.province_name,
             case when p.gender = 'M' then 1 else 0 end as gender_no,
             count(*)                                   as cnt
      FROM province_names pn
               INNER JOIN patients p
                          ON p.province_id = pn.province_id
      group by pn.province_names, p.GENDER) res
where res.gender_no = 1
  and cnt > (select count(*)
             from province_names pnn
                      INNER JOIN patients pp
                                 ON pp.province_id = pnn.province_id
             where res.province_name = pnn.province_name
               and pp.GENDER = 'F');