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) > 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) >( COUNT(s.serviceid) / COUNT(DISTINCT s.aircraftid) ) GROUP BY a.aircraftid