AnalysisException: subqueries are not supported in the select list

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.

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