Showing results for 
Search instead for 
Did you mean: 

mySQL Stock Vs Sold

mySQL Stock Vs Sold

Hi. I am trying to use SQL to get a list of all products in the catalogue and the number sold as I am interested to see products that have never sold. I have come across the below, but this will only list products that have sold, but I can see poor performing products. Ideally i want to see products that have never sold.


I can cross reference the inventory as the inventory is no managed.


SELECT, sales_flat_order_item.sku, sum(sales_flat_order_item.qty_ordered) As Qty_Sold, 
                          cataloginventory_stock_item.qty AS On_Hand FROM sales_flat_order_item, catalog_product_entity_varchar, 
                          catalog_product_entity, cataloginventory_stock_item 
                          WHERE catalog_product_entity_varchar.attribute_id = 71 AND catalog_product_entity_varchar.entity_id = cataloginventory_stock_item.product_id AND catalog_product_entity_varchar.entity_id = catalog_product_entity.entity_id AND
                          sales_flat_order_item.sku = catalog_product_entity.sku AND sales_flat_order_item.price > 0 
                         GROUP BY sales_flat_order_item.sku ORDER BY sales_flat_order_item.sku
Tags (1)

Re: mySQL Stock Vs Sold



Try with sales_flat_order_item table:


SELECT e.* FROM catalog_product_entity e LEFT JOIN sales_flat_order_item i ON e.entity_id = i.product_id WHERE i.product_id IS NULL


This query returns all never ordered products


Best Regards,