How to return results from a subquery greater than a value

I’m trying to find measuring component ids that show up at least 3 times. At this time it is throwing the Oracle Error “ORA-00936: missing expression.” I have tried putting in an existing statement to possibly satisfy the missing expression error, to no avail. It either returned too many values or didn’t bring back any values at all. How do I fix it to bring back results where my measuring component column brings back values that occur at least 3 times.

select td.td_entry_id, td.complete_dttm, imd.init_msrmt_data_id, imd.measr_comp_id,
imd.bus_obj_cd, imd.bo_status_cd, imd.status_upd_dttm, rep.last_name FROM
ci_td_entry td,
ci_td_drlkey drill,
d1_init_msrmt_data imd,
sc_user rep
WHERE td.td_type_cd ='D1-IMDTD'
and td.entry_status_flg = 'C'
and imd.init_msrmt_data_id = drill.key_value
and td.td_entry_id = drill.td_entry_id
and imd.bo_status_cd in ('DISCARDED','REMOVED')
and td.complete_user_id = rep.user_id
and td.complete_dttm >= '01-MAY-21'
and (select count(*)
    from d1_init_msrmt_data imd
    group by imd.measr_comp_id
    HAVING COUNT(*) > 3);

Answer

My original query works using the group by and having function once you add in the expression exists. What needed to be done to have the subquery actually work though was to correlate it to the main query. This was done by adding in the date parameter found in the main query, the parameter looking for “discarded and removed” and also by creating an inner join in the sub query itself, which joined the table used in the sub query to the table in the main query. The final result looked like this:

SELECT
    td.td_entry_id,
    td.complete_dttm,
    imd.init_msrmt_data_id,
    imd.measr_comp_id,
    imd.bus_obj_cd,
    imd.bo_status_cd,
    imd.status_upd_dttm,
    rep.last_name
FROM
    ci_td_entry          td,
    ci_td_drlkey         drill,
    d1_init_msrmt_data   imd,
    sc_user              rep
WHERE
    td.td_type_cd = 'D1-IMDTD'
    AND td.entry_status_flg = 'C'
    AND imd.init_msrmt_data_id = drill.key_value
    AND td.td_entry_id = drill.td_entry_id
    AND td.complete_dttm = imd.status_upd_dttm
    AND imd.bo_status_cd IN (
        'DISCARDED',
        'REMOVE'
    )
    AND td.complete_user_id = rep.user_id
    AND EXISTS (
        SELECT
            COUNT(*)
        FROM
            d1_init_msrmt_data imd2
        WHERE
            imd.measr_comp_id = imd2.measr_comp_id
            AND imd2.status_upd_dttm >= '01-JUN-21'
            AND imd2.bo_status_cd IN (
                'DISCARDED',
                'REMOVE'
            )
        GROUP BY
            imd2.measr_comp_id
        HAVING
            COUNT(*) >= 3
    );

I appreciate everyone’s help in getting this result.