asked on

ERROR 1111: Invalid Use of Group Function trying to update a mysql 4.1.10 table

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
(
last_name VARCHAR(100),
first_name VARCHAR(100),
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);

update jentest,tutorial_attempt
set tut_count=count(distinct(tutorial_id))
where jentest.user_id=tutorial_attempt.user_id

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!

ASKER CERTIFIED SOLUTION

todd_farmer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Arty K

Try todd_farmer's solution,

you can COUNT() only in SELECT content, that's why you have illegal usage.

AaronAbend

You could create a view with the aggregate....

create view tutorial_attempt_view as select user_id, count(distinct(tutorial_id)) as distinct_tut_ids group by user_id

update jentest,tutorial_attempt_view
set tut_count=distinct_tut_ids
where jentest.user_id=tutorial_attempt_view.user_id ;

AaronAbend

Sorry, my view sql was incorrect...missing the table. Here is the correct version:

create view tutorial_attempt_view
as select user_id, count(distinct(tutorial_id)) as distinct_tut_ids
from tutorial_attempt
group by user_id

jennfir

ASKER

Thanks! I tried the view, I don't think that views work in mysql v4.1.10, it looks like they only apply to v5. I tried Todd's suggestion, and that worked with a minor tweak to remove the jentest table from the from clause as apparently one cannot update a table and use it in the from clause as well.

So, I am doing much better, but I still have a minor issue, I want to make the query a little more specific to only select a particular type of tutorial, and ones that the user completed. BUT, as soon as I add the 'ta' to qualify the tutorial_attempt table so I can include the additional joins, the update comes up with incorrect numbers. Eg. if I run the below I get a tut_count of 499 for all rows, how in the world could simply adding 'as ta' cause the calculation be wrong?

update jentest,tutorial_attempt
set tut_count=(SELECT count(distinct(ta.tutorial_id)) FROM tutorial_attempt as ta
--join lessonplan.tutorial as t on t.tutorial_id=ta.tutorial_id
--where ta.attempt_end is not null
where jentest.tut_user_id=tutorial_attempt.user_id)
where jentest.tut_user_id=tutorial_attempt.user_id;

Please let me know if I should post this as a new question. Thanks so much for all your help!

todd_farmer

Your query above doesn't make sense to me. You have three WHERE clauses.

jennfir

ASKER

sorry, the 3rd from the bottom is commented out, I should of left that out. The query that works is what you had originally suggested, eg;

update jentest,tutorial_attempt
set all_count=(SELECT count(distinct(tutorial_id)) FROM tutorial_attempt
where jentest.tut_user_id=tutorial_attempt.user_id and attempt_end is not null )
where jentest.tut_user_id=tutorial_attempt.user_id;

however when I try to make it more distinct, eg. so I can add the joins, if I just add the 'ta' qualification to the tutorial attempt table on line 2, the resulting count is wrong, eg. changed the above which works fine to the below, it puts '488' as the count on all rows;

update jentest,tutorial_attempt
set all_count=(SELECT count(distinct(ta.tutorial_id)) FROM tutorial_attempt as ta
where jentest.tut_user_id=tutorial_attempt.user_id and attempt_end is not null )
where jentest.tut_user_id=tutorial_attempt.user_id;

Makes no sense to me at all how just adding ta would change the resulting count.

Thanks
Jen

todd_farmer

Try this:

update jentest,tutorial_attempt
set all_count=(SELECT count(distinct(ta.tutorial_id)) FROM tutorial_attempt as ta
where jentest.tut_user_id=ta.user_id and attempt_end is not null )
where jentest.tut_user_id=tutorial_attempt.user_id;

jennfir

ASKER

Thanks - I just had changed it all to fully qualify it, eg. very similiar to what you recommended, I used 'tutorial_attempt' instead of ta and that worked fine. I think its working now, just need to verify the data, thanks for your help!!!