PHP: Magento – current stock value

August 13th, 2011 by Richy B. Leave a reply »

If you run the Magento ecommerce shopping cart software and you want to find out how much your stock is worth, how many product lines you have stocked and how many individual items you have, you may find the following MySQL query handy.

I’m assuming that you’ve created an attribute with the attribute code “supplier_price” as a decimal, that you’ve kept this field up to date and your stock levels are accurate 😀 If it isn’t called “supplier_price” change the appropriate part in the query.

SELECT
COUNT(sku) AS 'products_stocked',
SUM(qty) AS 'items_in_stock',
SUM(stockvalue) AS 'stock_value'
FROM (

SELECT
catalog_product_entity.sku,
cataloginventory_stock_item.qty,
catalog_product_entity_decimal.value,
(cataloginventory_stock_item.qty * catalog_product_entity_decimal.value) AS stockvalue
FROM
cataloginventory_stock_item,
catalog_product_entity,
eav_attribute,
catalog_product_entity_decimal
WHERE
catalog_product_entity.type_id='simple' AND
cataloginventory_stock_item.product_id=catalog_product_entity.entity_id AND
catalog_product_entity_decimal.entity_id=catalog_product_entity.entity_id AND
eav_attribute.attribute_id=catalog_product_entity_decimal.attribute_id AND
eav_attribute.attribute_code='supplier_price' AND
cataloginventory_stock_item.qty > 0
ORDER BY sku ASC) AS b

This post is over 6 months old.

This means that, despite my best intentions, it may no longer be accurate.

This blog holds over 12 years of archived content - during that time, I may have changed my opinion of something, technology will have advanced (and old "best standards" may no longer be the case), my technology "know how" has improved etc etc - it would probably take me a considerable amount of time to update all the archival entries: and defeat the point of keeping them anyway.

Please take these posts for what they are: a brief look into my past, my history, my journey and "caveat emptor".

Comments are closed.

gamy-dance
%d bloggers like this: