SELECT sum(rds.iQuantity) as iQuantity,sum(rds.iMoney) as iMoney,
--sum(rds.iMoney)/sum(rds.iQuantity) as iUnitCost,
BA_Inventory.cInvCode,BA_Inventory.UFcInvCode,BA_Inventory.cInvStd,BA_Inventory.cInvName,BA_Inventory.cComUnitFROM RdRecords rds
INNER JOIN RdRecord rdr ON rds.[ID]=rdr.[ID]
INNER JOIN BA_WareHouse ON rdr.cWhCode=BA_WareHouse.cWhCode
INNER JOIN BA_Inventory ON rds.cInvCode=BA_Inventory.cInvCodeWHERE iDate>='2010-5-1' AND iDate<='2010-5-31'
AND CAST(iQuantity AS FLOAT)<>0 --这里的过滤条件问题
GROUP BY BA_Inventory.cInvCode,BA_Inventory.UFcInvCode,BA_Inventory.cInvStd,BA_Inventory.cInvName,BA_Inventory.cComUnit
ORDER BY iquantity ASC
这样查询出来的iQuantity 字段有零,sum(rds.iMoney)/sum(rds.iQuantity) as iUnitCost这个计算就报错,我想把为零的过滤掉,AND CAST(iQuantity AS FLOAT)<>0,这个条件不起作用呢?
select sum(iQuantity) case 0 then 0 else sum(isnull(rds.iMoney,0))/sum(rds.iQuantity) end as iUnitCost from ...
AND CAST(iQuantity AS FLOAT)<>0 --这里的过滤条件问题
GROUP BY BA_Inventory.cInvCode,BA_Inventory.UFcInvCode,BA_Inventory.cInvStd,BA_Inventory.cInvName,BA_Inventory.cComUnit
ORDER BY iquantity ASC
改成
WHERE iDate>='2010-5-1' AND iDate<='2010-5-31'
GROUP BY BA_Inventory.cInvCode,BA_Inventory.UFcInvCode,BA_Inventory.cInvStd,BA_Inventory.cInvName,BA_Inventory.cComUnit
having SUM(iQuantity)<>0 --这里的过滤条件问题
ORDER BY iquantity ASC
having sum(rds.iQuantity) <> 0
用1楼的或者
isnull(sum(rds.iQuantity),1)