CREATE VIEW view_warehousewise_stock AS SELECT SL.product_id,S.part_number,S.description,S.bar_code,S.brand_class_id AS brand_id,B.product_class AS brand,SL.warehouse_id,S.alias_name,S.size_id,size.size_name,S.colour_code_id,CS.color_name,S.model_id,MN.model_name,
SUM(SL.quantity*SL.unit_factor) AS quantity,S.isActive,S.inventory_item_type,S.rate,S.selling_price_1,S.selling_price_2,U.name AS unit_name,S.generic_id,G.generic_name
FROM erp_stock_ledger SL 
LEFT JOIN erp_store S on SL.product_id = S.id 
LEFT JOIN erp_product_class B ON B.id = S.brand_class_id 
LEFT JOIN erp_unit_of_measurement U ON U.product_id = S.id
LEFT JOIN  erp_inventory_generic_name G ON  G.id = S.generic_id
LEFT JOIN  erp_mas_store_size size ON S.size_id=size.id
LEFT JOIN  erp_mas_store_color CS ON S.colour_code_id = CS.id 
LEFT JOIN  erp_mas_store_model_name MN ON S.model_id = MN.id
group by SL.warehouse_id,SL.product_id 
UNION ALL 
SELECT S.id AS product_id,S.part_number,S.description,S.bar_code,S.brand_class_id AS brand_id,B.product_class AS brand,"0" AS warehouse_id,S.alias_name,S.size_id,size.size_name,S.colour_code_id,CS.color_name,S.model_id,MN.model_name,
"0" AS quantity,S.isActive,S.inventory_item_type ,S.rate,S.selling_price_1,S.selling_price_2,U.name AS unit_name,S.generic_id,G.generic_name
FROM erp_store S 
LEFT JOIN erp_product_class B ON B.id = S.brand_class_id 
LEFT JOIN  erp_inventory_generic_name G ON  G.id = S.generic_id
LEFT JOIN erp_unit_of_measurement U ON U.product_id = S.id
LEFT JOIN  erp_mas_store_size size ON S.size_id=size.id
LEFT JOIN  erp_mas_store_color CS ON S.colour_code_id = CS.id 
LEFT JOIN  erp_mas_store_model_name MN ON S.model_id = MN.id
ORDER BY `quantity` ASC