Try replacing SUM(Value) with SUM(COALESCE(nullif(ChageValue,0),value))
Select paidCustomerCode,count(tblComparePods.hAWBNum) As hAWBNum,Sum(weight) As weight,SUM(COALESCE(nullif(ChageValue,0),value)) from tblOutboundHeader INNER JOIN tblComparePods ON tblOutboundHeader.hAWBNum=tblComparePods.hAWBNum WHERE (Date>='2013/11/15' AND Date<='2013/11/30') AND tblComparePods.isconfirm ='0' AND paidCustomerCode= '0001' Group by paidCustomerCode
Note: I have not tested with sample data just checked for syntax errors. NULLIF is available only from 2005 onwards.