SELECT t1.sa_itemgroupid,max(if(t1.siteid='ccyosg',(if(ifnull(t8.price,0)!=0 && t8.price<=t7.price,t8.price,t7.price)),t7.price)) maxprice,min(if(t1.siteid='ccyosg',(if(ifnull(t8.price,0)!=0 && t8.price<=t7.price,t8.price,t7.price)),t7.price)) minprice FROM sa_itemgroupmx t1 LEFT JOIN plm_item t2 ON t1.itemno = t2.itemno AND t1.siteid = t2.siteid LEFT JOIN sa_itemsaleclass t4 ON t2.itemid = t4.itemid AND t2.siteid = t4.siteid LEFT JOIN plm_itemclass t5 ON t5.itemclassid = t4.itemclassid AND t5.siteid = t4.siteid LEFT JOIN plm_unit t6 ON t6.unitid = t2.unitid AND t6.siteid = t2.siteid left join sa_itemprice t7 on t2.itemid = t7.itemid and t7.pricegrade = $pricegrade$ left join sa_itemprice t8 on t2.itemid = t8.itemid and t8.pricegrade =6 where t5.itemclassid in $itemclassid$ and t1.siteid=$siteid$ and ifnull(t2.isonsale,0)=1 group by t1.sa_itemgroupid