|
|
Posted by Hilarion on 11/16/05 13:12
> Currently, all of the products to be ordered are listed including...
>
> Product code (product)
> Description (title)
> Quantity (quantity)
> Price (price)
> Order ID (cart_order_id)
>
> If there was one order that contained the product "Apple Brown Betty" with a
> quantity of 1, and a second order that contained the product "Apple Brown
> Betty" with a quantity of 2...they currently show up as two different lines
> in the list. I would like the product "Apple Brown Betty" to show up once in
> the list with a quantity of 3 (the total quantity we need to order from our
> supplier).
>
> What would the query be to produce this result?
>
> I assume I would no longer be able to have the Order ID in the list as the
> total of 3 would come from two different orders...or can both Order ID's be
> listed along with the product total?
>
> The current query used is...
>
> $sql_select = mysql_query(
> 'SELECT product, title, quantity, price, cart_order_id ' .
> 'FROM ' . $prefix . 'store_order_inv ' .
> 'WHERE order_from_supplier = 1 ' .
> 'ORDER BY product ASC'
> );
Are you sure that all orders of "Apple Brown Betty" have exactly same
values in "product", "title" and "price" fields? If they do not (or they
are same, but CAN differ because there's no mechanism to prevent
this), then the problem is a bit more complex.
I'm not sure what does "title" and "price" contain. I understand
that "product" is a unique identifier of the ordered product,
but if "title" and "price" can differ between orders of same
product (same "product" value), then you have same problem with
them as with "cart_order_id".
In standard SQL the query which would return quantity to be ordered
for each "product" would look like this:
SELECT product, SUM( quantity ) AS sum_quantity
FROM store_order_inv
WHERE order_from_supplier = 1
GROUP BY product
ORDER BY product ASC
If "title" and "price" is always same for same "product", then
you could add them to the query:
SELECT product, title, price, SUM( quantity ) AS sum_quantity
FROM store_order_inv
WHERE order_from_supplier = 1
GROUP BY product, title, price
ORDER BY product ASC
but if you'll have two orders which have same "product" but
different "title" or "price", then that will cause the
above query to return results divided between those
differing sets of values.
If "title" contains name of the product, then you should
consider changing your database design to more normalized.
You could move product names to another table like:
CREATE TABLE products (
product_code VARCHAR(20) NOT NULL PRIMARY KEY,
name VARCHAR(200) NOT NULL
)
The table "store_order_inv" would then refer to the "products"
table with the "product" field.
Hilarion
Navigation:
[Reply to this message]
|