Keep getting errors when using having clause with aggregate functions

[Omoruyi]

Hi guys, i need to finish an assignment but keep getting the error: ERROR 1111 (HY000): Invalid use of group function

 

The assignment goes as follows: Give a list of cruises (c_code, description) for all cruises and their number of passengers that have more passengers then average.

 

There are 3 tables in the database: Booking, cruises and passengers.

 

My code is :SELECT C_CODE, C_DESCRIPT, COUNT(DISTINCT(B_PASS)) FROM CRUISE, BOOKING WHERE (CRUISE.C_CODE =BOOKING.B_CRUISE) GROUP BY C_CODE HAVING COUNT(DISTINCT(B_PASS)) > AVG(COUNT(DISTINCT(B_PASS)));

 

Obviously I'm doing something wrong..

Does anyone know what I'm doing wrong ?

 

 

thanks in advance,

Omoruyi

[justsomeguy]

I believe you also need to group by c_descript. You generally need to group by any column that is not used in an aggregate function.

[Omoruyi]

I believe you also need to group by c_descript. You generally need to group by any column that is not used in an aggregate function.

Nope that didnt work neither ..

[justsomeguy]

The problem is the AVG function. There's a problem using a function like that with the GROUP BY. You're already getting the count of certain records, you can't also get the average of the entire table in the same query like that. You might need to use a subquery to calculate the average separately.