[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:
`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:

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

What version of mysql are you using? I tried a similar query in one of my db's and got no errors:

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... ??

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 ;)

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.

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