Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2010
    Posts
    3

    Unanswered: Invalid use of group function

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by skl25 View Post
    Delete the shipments with quantity less than the average for each project.
    nice homework assignment

    you need to select the average in a subquery

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2010
    Posts
    3
    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!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Perhaps the average value could be stored in a variable?
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Try reading up on correlated subqueries.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2010
    Location
    Deer Park, Texas
    Posts
    1
    the average value could be stored in a variable?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Danielle24 View Post
    the average value could be stored in a variable?
    you catch on pretty quick
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by r937 View Post
    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
    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.

  10. #10
    Join Date
    Aug 2010
    Posts
    3
    Thanks everyone for your advice!

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937 View Post
    and how does a sql server documentation page help this particular mysql scenario?
    The SQL Server error message ties them together, at least indirectly.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •