How to write this MySQL query,?

Question

There is the table 'treatment' that contains the columns, 'staff_no', 'patient_id', 'start_date', 'reason'. Now, I have to select a query to get the number of distinct patients undergoing each type of treatment (ie reason for treatment) in the database, ordered alphabetically by the reason for the treatment but only have to list the treatments that have at least 2 patients undergoing them. This is what I wrote-

SELECT COUNT (DISTINCT patient_id) CountNumber, reason FROM 'treatment' GROUP 
BY reason ORDER BY reason;

but i get the #1064error.

score 0 · Answer 1 · answered Dec 11 '17 at 22:18

Maybe something like this, exists subquery will select all reasons which have more than 2 rows in group, from that select distinct will make list of unique reasons and patient_id and outer select will show reasons sorted with number of patients for that reason

SELECT `reason`, COUNT(*) FROM 
 (
      SELECT DISTINCT `patient_id`, `reason` FROM `treatment` 
      WHERE EXISTS  (
            SELECT `reason` FROM `treatment` t2 
                WHERE `treatment`.`reason` = `t2`.`reason` 
               GROUP BY `reason`
               HAVING COUNT(*)>=2
      )
 ) t 
 GROUP BY reason ORDER BY `reason`

HAVING is kind of condition but for aggregate functions like COUNT, AVG and so

How to write this MySQL query,?

1 Answers1