Invalid Use of Group Function Using Join SELECT
    a.aircraftid 'Aircraft ID',
    COUNT(s.serviceid)
FROM
    aircraft a
JOIN service s ON
    a.aircraftid = s.serviceid
JOIN(
    SELECT
        (
            COUNT(ser.serviceid) / COUNT(DISTINCT ser.aircraftid)
        ) AS Aver
    FROM
        service ser
) AS se
ON
    COUNT(s.serviceid) &gt; se.Aver
GROUP BY
    a.aircraftid


I have been trying to figure out why I am getting the Invalid Use Of Group Function.

What I am trying to do is show the aircraft ID and how many services it has had if the number of services is above the average number of services.

The problem is that I use group by to count the number of services per aircraft. I tried to have the average in the where clause, but I also got an error using the following code:

SELECT
    a.aircraftid,
    COUNT(s.serviceid)
FROM
    aircraft a,
    service s
WHERE
    COUNT(s.serviceid) &gt;(
        COUNT(s.serviceid) / COUNT(DISTINCT s.aircraftid)
    )
GROUP BY
    a.aircraftid
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 )