CODEWITHSUNDEEP
mysql
Guruprasad Hegde
Guruprasad Hegde

Reputation: 9

I need a solution for this SQL query problem

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

Upvotes: 0

Views: 697

Answers (3)

Ozan Sen
Ozan Sen

Reputation: 2615

You can USE CTE to achieve your goal if your MySQL version is 8.0.X.X and more!!

(Run this command to check it:)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.00 sec)

Full Code:

WITH CTE AS (SELECT province_name, COUNT(*) AS MaleCount FROM patients p JOIN province_names pn ON p.province_id = pn.province_id WHERE gender = 'M' GROUP BY province_name)
, CTE2 AS (SELECT province_name, COUNT(*) AS FemaleCount FROM patients p JOIN province_names pn ON p.province_id = pn.province_id WHERE gender = 'F' GROUP BY province_name) 
SELECT CTE.province_name FROM CTE JOIN CTE2 ON CTE.province_name = CTE2.province_name WHERE MaleCount > FemaleCount;

Upvotes: 0

reza ramezani matin
reza ramezani matin

Reputation: 1474

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

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

Upvotes: 2

Related Questions