ERROR 1111: Invalid Use of Group Function trying to update a mysql 4.1.10 table
Posted on 2006-03-28
Hi - this is probably a simple question, but I have spent some time trying to fix it, and am getting nowhere fast ):
I have a table 'jentest' which I need to update a column 'tut_count' with the number of distinct 'tutorial_id' rows per user in another table. Everytime I have tried to do so, I get error 1111: invalid use of group function, but I do not understand why. Here is the def for table 'jentest' ;
create table jentest
user_id INTEGER UNSIGNED NOT NULL PRIMARY KEY,
tut_user_id INTEGER UNSIGNED NOT NULL,
quizz_count INTEGER UNSIGNED,
tut_count INTEGER UNSIGNED
the table tutorial_attempt includes one row each time a user attempts a tutorial. The important columns are user_id(which is the same id in jentemp) and tutorial_id which identifies the tutorial being taken. For example, user 123 might have 100 rows in this tutorial_attempt table, but only 75 distinct tutorial_id's indicating he took 25 tutorials twice. What I want to do is update jentest with a total # of distinct tutorials for each user, eg. in the above example, I should value tut_count with the number 75 for user_id 123. I am trying this(and about 20 other variations, but this seems closest to what I want);
And I get the error 1111, but where is the invalid use of group function? How else can I do this?
I believe we are using mysql v4.1.10a.
Thanks in advance!