cancel
Showing results for 
Search instead for 
Did you mean: 

mySQL Stock Vs Sold

Highlighted

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.name, 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)
1 REPLY

Re: mySQL Stock Vs Sold

Hello

 

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,