Most of the products I have are listed with attributes, eg a kitchen handle will be available in several length and each length is sold at a different price.

Often I need to know the sales quantity by attribute of a certain product to find out the sales movements on the different sizes of a kitchen handle. This is not quite straight forward since there are a few tables involved.

So the result we’re looking for is something like this:

To use this SQL database query you will need to go http://your-domain/cpanel > phpmyadmin and selecet your Zen-Cart database. To run this report you will need to know the product_id of the product in question, in my case here it’s 273 (Zen-Cart Admin panel > Catalog > Products/Categories) > relevant category and the ids are listed at the beginning of each product). Click on the SQL Tab at the top of the screen in phpmyadmin and copy and paste the SQL query:

SELECT op.products_id AS “Product ID”, sum( op.products_quantity ) AS Qty, pov.products_options_values_name AS Attribute
FROM zen_orders_products AS op, zen_orders AS o, zen_orders_products_attributes AS pa, zen_products_options_values AS pov
WHERE op.orders_id = o.orders_id
AND op.orders_products_id = pa.orders_products_id
AND pa.products_options_values_id = pov.products_options_values_id
AND o.date_purchased >= “2010-01-01″
AND o.date_purchased <= “2010-05-26″
AND products_id =273
GROUP BY pa.products_options_values_id
ORDER BY Qty DESC

and now all you need to do is to change the products_id to the ID you want and change the purchase dates as required and click GO.

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.

Tagged as: SQL