@EvaF This is the query I am getting in a category:
SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, cp.position, warehousequantity FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute AND sa.id_shop = 1 AND sa.id_shop_group = 0 ) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 1 AND ps.active = TRUE) JOIN ( SELECT aps.id_product, concat(aps.warehouse+100000,110000000-asa.quantity ) as warehousequantity FROM ps_product_shop aps INNER JOIN ps_stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product WHERE p.visibility IN ('both', 'catalog') AND c.nleft>=140 AND c.nright<=141 AND ps.id_shop='1' GROUP BY p.id_productSELECT p.id_product, warehousequantity FROM (SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, cp.position, warehousequantity FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute AND sa.id_shop = 1 AND sa.id_shop_group = 0 ) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 1 AND ps.active = TRUE) JOIN ( SELECT aps.id_product, concat(aps.warehouse+100000,110000000-asa.quantity ) as warehousequantity FROM ps_product_shop aps INNER JOIN ps_stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product WHERE p.visibility IN ('both', 'catalog') AND c.nleft>=140 AND c.nright<=141 AND ps.id_shop='1' GROUP BY p.id_product) p INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) JOIN ( SELECT aps.id_product, concat(aps.warehouse+100000,110000000-asa.quantity ) as warehousequantity FROM ps_product_shop aps INNER JOIN ps_stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product GROUP BY p.id_product ORDER BY p.position ASC LIMIT 0, 24SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, cp.position, warehousequantity FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute AND sa.id_shop = 1 AND sa.id_shop_group = 0 ) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 1 AND ps.active = TRUE) JOIN ( SELECT aps.id_product, concat(aps.warehouse+100000,110000000-asa.quantity ) as warehousequantity FROM ps_product_shop aps INNER JOIN ps_stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product WHERE p.visibility IN ('both', 'catalog') AND c.nleft>=140 AND c.nright<=141 AND ps.id_shop='1' GROUP BY p.id_productSELECT COUNT(DISTINCT p.id_product) c, warehousequantity FROM (SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, cp.position, warehousequantity FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute AND sa.id_shop = 1 AND sa.id_shop_group = 0 ) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 1 AND ps.active = TRUE) JOIN ( SELECT aps.id_product, concat(aps.warehouse+100000,110000000-asa.quantity ) as warehousequantity FROM ps_product_shop aps INNER JOIN ps_stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product WHERE p.visibility IN ('both', 'catalog') AND c.nleft>=140 AND c.nright<=141 AND ps.id_shop='1' GROUP BY p.id_product) p JOIN ( SELECT aps.id_product, concat(aps.warehouse+100000,110000000-asa.quantity ) as warehousequantity FROM ps_product_shop aps INNER JOIN ps_stock_available asa ON aps.id_product = asa.id_product ) ws ON p.id_product = ws.id_product
When I try to run it in phpMyAdmin, I am getting this error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'p.id_product, warehousequantity FROM (SELECT p.id_product, p.id_manufacturer, SU' at line 1
By the way, I tried to run your query sorted by warehousequantity column DESC, and the results are not correct (see the attached pic please).