0
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
  • Please provide a fiddle with tables DDLs, some sample data, and show desured result for that data. And specify MySQL version. – Akina Oct 23 at 9:09
  • GROUP BY is logically applied after the FROM and WHERE clauses are processed, so attempting to use aggregate functions in join conditions and query criteria does not make sense. – mustaccio Oct 23 at 12:45
1

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) > (   SELECT  COUNT(serviceid) / COUNT(DISTINCT aircraftid)
                                FROM    service )
  • Thank you so much. It has returned the desired output. I guess that bit for the average needed to be in a sub query – Hamish Lothian Oct 24 at 0:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.