ENGINE = MEMORY
SELECT COL_WAR_ID AS TEMP_WAR_ID, LOCW_ID AS TEMP_LOCW_ID, COL_COLLECTION_GOO_CODE AS TEMP_GOO_CODE, MIN(AMOUNT) AS TEMP_AMOUNT
FROM (
SELECT COL_ID, COL_COLLECTION_GOO_ID, COL_WAR_ID, LOCW_ID, COL_COLLECTION_GOO_CODE, COL_ITEM_GOO_CODE,
FLOOR(GREATEST(AM_COL.GSTA_AMOUNT, COALESCE(SUM(AM_ALT.GSTA_AMOUNT), 0)) / COL_ITEM_PCS) AS AMOUNT
FROM GOODS
JOIN COLLECTION ON GOO_CODE = COL_COLLECTION_GOO_CODE AND COL_WAR_ID = GOO_WAR_ID
JOIN LOCAL_WAREHOUSE ON COL_WAR_ID = LOCW_WAR_ID
JOIN GOODS_STOCK_AMOUNT AS AM_COL ON AM_COL.GSTA_GOO_CODE = COL_ITEM_GOO_CODE AND AM_COL.GSTA_LOCW_ID = LOCW_ID
LEFT JOIN COLLECTION_ALT ON COL_ID = CALT_COL_ID
LEFT JOIN GOODS_STOCK_AMOUNT AS AM_ALT ON AM_ALT.GSTA_GOO_CODE = CALT_ALT_GOO_CODE AND AM_ALT.GSTA_LOCW_ID = LOCW_ID
WHERE GOO_EXPLODE_COLLECTION AND NOT COL_DELETED AND GOO_WAR_ID = '1' AND LOCW_ID = '2' AND COL_COLLECTION_GOO_CODE IN ('setm008','s1col01','s2col01','s3col01','sethop1','ipcol01','111_','111d','setw141','217_1','216','217','seteu90','scol11','scol12','217_2','217_3','217x','shome1','home80','nsd73','basic','business','elite','save51','s1it20','antiviral')
GROUP BY COL_ID, LOCW_ID) AS SOURCE
GROUP BY COL_COLLECTION_GOO_CODE, LOCW_ID