Is there a simple SQL query I can run to find any ENABLED product in Magento that has a salable quantity which is 0?

We have thousands of products and in the back end product grid we can sort and range by Quantity, don't have the ability to filter or sort but via Salable Quantity column which is super frustrating, and there is many inaccuracies we are trying to resolve and its very time consuming to go through page after page in the grid.


1 Answer 1


Try below query which will return the enabled products having 0 salable quantity.

SELECT t1.`sku`, (SUM(t1.`quantity`) + COALESCE(SUM(t2.`quantity`), 0)) AS `salable_quantity` FROM `inventory_source_item` t1 LEFT JOIN `inventory_reservation` t2 ON t1.`sku` = t2.`sku` AND t1.`status` = 1 GROUP BY t1.`sku` HAVING `salable_quantity` = 0 AND t1.`sku` NOT IN (SELECT `sku` FROM `catalog_product_entity` WHERE `row_id` IN (SELECT `row_id` FROM `catalog_product_entity_int` WHERE `attribute_id` = (SELECT `attribute_id`  FROM `eav_attribute` WHERE `attribute_code` = 'status' AND `entity_type_id` = 4) AND `catalog_product_entity_int`.value = 2));
  • This worked! Thank you 🙏
    – jvb8
    Mar 24, 2022 at 1:28
  • 1
    Your welcome! Can you please accept this as answer and up-vote it. Thanks! Mar 24, 2022 at 5:26
  • Would love to up-vote but i'm new and don't have enough reputation to action. If you can up-vote my question, i'll be sure to return in kind once my reputation score allows me to!
    – jvb8
    Mar 24, 2022 at 8:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.