• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 596
  • Last Modified:

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!
0
jennfir
Asked:
jennfir
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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
 
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
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now