I get this error code shown in title when using this following query. I’m trying query two tables to find total patients with hearing issues and the total of those patients with hearing issues who have undergone some sort of scan (MR,SC,CT).
SELECT (
SELECT COUNT(*)
FROM hearing_evaluation
where severity_of_hearing_loss <> 'Normal'
AND severity_of_hearing_loss <> 'insufficient data'
) AS patients_with_hearing_loss
, AVG(number_of_scans) AS avg_number_of_scans
FROM (
SELECT patient_id, COUNT(*) AS number_of_scans
from imaging
where patient_id IN (
SELECT patient_id
from hearing_evaluation
where severity_of_hearing_loss <> 'Normal'
and severity_of_hearing_loss <> 'insufficient data'
)
AND modality IN ('CT','MR','SC')
GROUP BY patient_id
) AS scans
Any help would be appreciated.
Advertisement
Answer
I tried, pls refer to below SQL – this will work in impala. Only issue i can see is, if hearing_evaluation has multiple patient ids for a given patient id, you need to de-duplicate the data.
There can be case when patient id doesnt exist in image table – in such case you need to apply RIGHT JOIN.
SELECT
COUNT(patient_id) AS patients_with_hearing_loss
, AVG(rs.number_of_scans) AS avg_number_of_scans
FROM (
SELECT i.patient_id patient_id, COUNT(*) AS number_of_scans
from imaging i ,hearing_evaluation h
where i. patient_id = h.patient_id
and h.severity_of_hearing_loss <> 'Normal'
and h.severity_of_hearing_loss <> 'insufficient data'
AND modality IN ('CT','MR','SC')
GROUP BY i.patient_id ) rs