11/29/2006
ZenCart Orders Total Weight Query
SELECT op.products_quantity * SUM( products_weight )
FROM products p
JOIN orders_products op
WHERE p.products_id
IN (
SELECT products_id
FROM orders_products
WHERE orders_id = 'ORDER_ID'
)
AND op.orders_id = 'ORDER_ID'
FROM products p
JOIN orders_products op
WHERE p.products_id
IN (
SELECT products_id
FROM orders_products
WHERE orders_id = 'ORDER_ID'
)
AND op.orders_id = 'ORDER_ID'
Replace ORDER_ID with the orders_id in question.
Requires MySQL 4.1 or better for subselect support
Related Link: Bowflex
Popularity: 13%
December 2nd, 2007 at 12:17 pm
your query does not count more than 1 items of the same products
more accurate query should be sth like this:
SELECT op.products_quantity*sum(p.products_weight)
FROM products p JOIN orders_products op
WHERE p.products_id IN (SELECT products_id FROM orders_products WHERE orders_id = ‘ORDER_ID’)
AND op.orders_id = ‘ORDER_ID’
December 3rd, 2007 at 11:10 am
Good catch!
I updated the original query to avoid confusion.