MariaDB 10.9

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


INSERT INTO `products` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);


CREATE TABLE `products_stores` (
  `id_product` int(11) NOT NULL,
  `id_variation` int(11) DEFAULT NULL,
  `id_store` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  UNIQUE KEY `products_stores_id_store_id_product_id_variation_uindex` (`id_store`,`id_product`,`id_variation`),
  KEY `products_stores_products_id_fk` (`id_product`),
  KEY `products_stores_id_store_index` (`id_store`),
  CONSTRAINT `products_stores_products_id_fk` FOREIGN KEY (`id_product`) REFERENCES `products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


INSERT INTO `products_stores` VALUES (1,NULL,1,1),(1,NULL,2,2),(1,NULL,3,3),(1,NULL,4,4),(2,NULL,1,2),(3,NULL,1,3),(4,NULL,2,2),(4,NULL,1,4);


Records: 9  Duplicates: 0  Warnings: 0



Records: 8  Duplicates: 0  Warnings: 0


SELECT 
    (
    SELECT
        SUM(ps3.quantity)
    FROM
        products_stores ps3 USE INDEX (products_stores_id_store_id_product_id_variation_uindex)
    WHERE
        ps3.id_product = p.id AND ps3.id_variation <=> ps.id_variation AND ps3.id_store = 1
) AS store3_quantity
FROM
    products_stores ps
LEFT JOIN products p ON
    p.id = ps.id_product;


store3_quantity


1

2

3

4

1

4

1

1


SELECT 
    (
    SELECT
        SUM(ps3.quantity)
    FROM
        products_stores ps3 USE INDEX (products_stores_id_store_index)
    WHERE
        ps3.id_product = p.id AND ps3.id_variation <=> ps.id_variation AND ps3.id_store = 1
) AS store3_quantity
FROM
    products_stores ps
LEFT JOIN products p ON
    p.id = ps.id_product;


store3_quantity


1

2

3

4

1

4

1

1