Is there a way?

[Anon]

Is there a way to count the number of diffent field value in a select statement?

ok, I have a bunch of orders with lots of different items in it. I want to find out the total quanities of each item in the results. How do I do this? I can store it in an array and match them, but is there a cleaner way to do this?

[rrijnders]

What do you mean when you say "...in a select statement"?

If your order_detail table is set up such that is has:

<font color=blue>
&nbsp;order_num
&nbsp;order_item
&nbsp;order_qty
</font>

then you can sum quantities for all items with:

<font color=blue>
SELECT sum(order_qty) AS totalqty FROM order_detail where order_num=$ordnum
</font>

where $ordnum is a variable holding the given order number.

-- Rich

[Anon]

I'm gettin an error:

Error in query select sum(ItemQuant) as qty from OrderDetail where ItemID='0' Unknown column 'ItemQuant' in 'field list'

there is a ItemQuant in my table

I'm using MySql
is this the problem?

thx for your suggestion

[Anon]

Does the column you use in the sum() have to be primary or index?

[rrijnders]

No, the field you are summing can be any numeric field in your db. Should be working if the column exists in the table.

-- Rich

[Anon]

but it does, y hmmm is giving me this problem

[Anon]

oops never mind my mistake, wrong spelling
thx for the help