Question 113010 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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') `
Tags:

3

Answers


  1. 0

    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);
    Login or Signup to reply.
  2. 0

    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');
    Login or Signup to reply.
  3. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search