一个简单的SUN()汇总后的过滤条件问题，在线等

[yumanqing]

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.cComUnit

FROM 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.cInvCode

WHERE 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,这个条件不起作用呢？

[xxccy]

3楼的正解

[饮水需思源]

--楼主使用的是U8的数据库？
select sum(iQuantity) case 0 then 0 else sum(isnull(rds.iMoney,0))/sum(rds.iQuantity) end as iUnitCost from ...
 

[昵称被占用了]

WHERE 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

改成

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

[rmljoe]

数量不为零 与 数量加总不为零是两个概念

在group by 语句后面加
having sum(rds.iQuantity) <> 0

[老黎]

用1楼的或者
isnull(sum(rds.iQuantity),1)

[htl258_Tony]

[Quote=引用 2 楼 haiwer 的回复:]
SQL code
WHERE 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……
[/Quote]UP

[andy_liucj]

[Quote=引用 2 楼 haiwer 的回复:]
SQL code
WHERE 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……
[/Quote]
公司的网络真恶心，我每回一次帖都需要登录一次