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!
jennfirAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

todd_farmerCommented:
Try this:

update jentest,tutorial_attempt
set tut_count=(SELECT count(distinct(tutorial_id)) FROM jentest, tutorial_attempt
where  jentest.user_id=tutorial_attempt.user_id)
where  jentest.user_id=tutorial_attempt.user_id;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NopiusCommented:
Try todd_farmer's solution,

you can COUNT() only in SELECT content, that's why you have illegal usage.
0
AaronAbendCommented:
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 ;
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

AaronAbendCommented:
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
0
jennfirAuthor Commented:
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!
0
todd_farmerCommented:
Your query above doesn't make sense to me.  You have three WHERE clauses.
0
jennfirAuthor Commented:
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
0
todd_farmerCommented:
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;

0
jennfirAuthor Commented:
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!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.