sqlite query python

i want to perform the below query

SELECT t1.patient_report, COUNT(*) AS cnt, t1.doctor_report, (SELECT t2.doctor_report FROM infoTable t2 WHERE t2.patient_report = t1.patient_report AND cnt > 1 LIMIT 3) AS Doctors FROM infoTable t1 WHERE t1.patient_report != 'N/A' GROUP BY t1.patient_report ORDER BY cnt DESC

but i got this error!

Result: no such column: cnt

please how can i solve the problem ?

Answer

The subquery can’t access the aliased column cnt of the outer query.
But even if it did have accesss, another error would be thrown because the subquery may return more than 1 rows.
I think that you can do what you want with window functions and GROUP_CONCAT():

SELECT t.patient_report, t.cnt,
       GROUP_CONCAT(doctor_report) AS Doctors
FROM (
  SELECT patient_report, 
         doctor_report,
         ROW_NUMBER() OVER (PARTITION BY patient_report) rn,
         COUNT(*) OVER (PARTITION BY patient_report) cnt
  FROM infoTable 
  WHERE patient_report <> 'N/A' 
) t
WHERE t.rn <= 3 AND t.cnt > 1
GROUP BY t.patient_report, t.cnt
ORDER BY t.cnt DESC