Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile [Solved] Sum and GROUP

    Hi all,

    I have a working knowledge of MySQL currently, but I decided to try expand it a bit on my latest project, and I've hit a bit of a wall.

    I have a table named `items`. It has the following fields:
    Code:
    `item_id`
    `item_name`
    `item_cost`
    `item_quantity`
    `item_desc`
    `item_cat`
    The main things to see here is that: item_quantity is the Quantity in stock. item_cat is the Category of the item.

    What I'm trying to do is run a SELECT to find the total quantity of items in each category, then have the query give me the top 5 stocked categories so I can run them through a loop with mysql_fetch_array(). The query I'm using is:
    Code:
    SELECT `item_cat`,sum(`item_quantity`) AS `total_quantity` FROM `items` GROUP BY `item_cat` ORDER BY `total_quantity` DESC LIMIT 0,5
    However, mysql_error() returns "Invalid use of group function".

    I've looked up some tutorials on GROUP and sum, but neither show how they should be used together.

    What am I doing wrong?

    Thanks for reading,
    Wrathi
    Last edited by Wrathi; 02-19-2007 at 12:19 PM.

  • #2
    New Coder
    Join Date
    Aug 2006
    Location
    Sweden
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What version of mysql are you using? I tried a similar query in one of my db's and got no errors:

    Code:
    SELECT `type`,sum(`min`) AS `foo`
    FROM `benchmark`
    GROUP BY `type`
    ORDER BY `foo` DESC
    LIMIT 0,5;
    The output is what i expected, didn't get no error... ??

  • #3
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the reply.

    According to phpinfo(); I'm running MySQL version "3.23.49".

    EDIT: Eep. PhpMyAdmin is telling me I'm running on "MySQL 4.0.24-standard".

    "3.23.49" is the Client API Version, apparently.

    EDIT: Solved! The problem was that I was doing GROUP BY `item_cat`, as opposed to GROUP BY 'item_cat'. It was simply the quotes I used.

    Thanks anyway
    Last edited by Wrathi; 02-19-2007 at 12:18 PM.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    You should be using this:

    GROUP BY item_cat

    and not either of:

    GROUP BY `item_cat` - which is okay but the backticks work only in mysql

    GROUP BY 'item_cat' - which you are grouping on a string and not a column

    Also note that your client version of mysql is extremely outdated.

  • #5
    New Coder
    Join Date
    Feb 2005
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Update to mysql 4+ if possible, that way you can use more database optimization functions. Last I recall, 3.x does not even have union
    Kenetix:: Achieving more than the ordinary.
    http://www.kenetix.net


  •  

    Posting Permissions

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