I am working on a code that has the following requirements:

Delete the shipments with quantity less than the average for each project.

I have written the code

DELETE FROM Shipments
WHERE quantity < AVG(quantity);

however I get the ERROW 1111 (HY000): Invalid use of group function.

Any help would be greatly appreciated.

nice homework assignment

you need to select the average in a subquery

Haha thanks. It makes me mad because I visualize how this works but I can't seem to get the average function to cooperate!

DELETE FROM Shipments
WHERE quantity < (SELECT AVG(quantity)
FROM Shipments);

That is what I have rewritten, however I get a new error message saying

ERROR 1093 (HY000): You can't specify target table 'Shipments' for update in FROM clause.

This makes sense to me since you keep updating the avg, but how else would you write it? Thanks again for your help!

Perhaps the average value could be stored in a variable?

Try reading up on correlated subqueries.

and how does a sql server documentation page help this particular mysql scenario?

george is right, skl25, you'll need to use a mysql user variable

MySQL :: MySQL 5.0 Reference Manual :: 8.4 User-Defined Variables

the average value could be stored in a variable?

you catch on pretty quick

It was just to show him how such queries work - I had no desire to do his homework for him. I will admit that I'd forgotten that MySQL won't allow a correlated query within a delete - shame as most other RDBMs do. I still wouldn't use variables though (I'd go for a temp table) or are you aiming for a NZDF style solution.

Thanks everyone for your advice!

The SQL Server error message ties them together, at least indirectly.

-PatP

um, which SQL Server error message?

the page mike linked to talked about correlated subqueries

the only error message in this thread was a mysql one

your reply seems to have had a NBDF (nothing but deviosity factor)