Invalid Use of Group Function Using Join
Try: 

SELECT  a.aircraftid 'Aircraft ID',
        COUNT(s.serviceid)
FROM    aircraft a
JOIN    service s
    ON  a.aircraftid = s.serviceid
GROUP BY    a.aircraftid
HAVING COUNT(s.serviceid) &gt; (   SELECT  COUNT(serviceid) / COUNT(DISTINCT aircraftid)
                                FROM    service )