Omoruyi Posted December 3, 2013 Share Posted December 3, 2013 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted December 3, 2013 Share Posted December 3, 2013 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. Link to comment Share on other sites More sharing options...
Omoruyi Posted December 3, 2013 Author Share Posted December 3, 2013 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 .. Link to comment Share on other sites More sharing options...
justsomeguy Posted December 3, 2013 Share Posted December 3, 2013 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now