SQL集团通过计算
问题描述:
在此先感谢..SQL集团通过计算
SELECT width, thickness, lengthfeet, lengthinch, Sum(nos), Sum(areasqft)
FROM materialsdetails
WHERE materialcode='" & Me.txtMaterialCode.Text & "'
GROUP BY width, thickness, lengthfeet, lengthinch
ORDER BY width, thickness, lengthfeet, lengthinch
我用上面的代码来获得的总和领域的“号”和组“areasqft”。
在'transactionsthrough'列中(请看图片)我已经输入'Purchase','Issue','Return'和'Damage'作为数据。
事实上,而不是Sum(nos)
和Sum(areasqft)
我只需要值作为 购买 - 发行+回车 - 伤害与查询相同Group by
领域。
答
您应该能够使用总和中的下面的if语句否定发布和损坏的值。
SELECT width
,thickness
,lengthfeet
,lengthinch
,SUM(nos*iif(or(transactionthrough="Issue",transactionthrough="Damage"),-1,1))
,SUM(areasqft*iif(or(transactionthrough="Issue",transactionthrough="Damage"),-1,1))
FROM materialsdetails
WHERE materialcode = '" & Me.txtMaterialCode.Text & "'
GROUP BY width
,thickness
,lengthfeet
,lengthinch
ORDER BY width
,thickness
,lengthfeet
,lengthinch
答
首先,按TransactionThrough:
create view byTT as
SELECT
transactionthrough,
sum(width) as width,
sum(thickness) as thickness,
-- etc for each column you care about
FROM materialsdetails
WHERE -- whatever where clause you want
group by transactionthrough;
然后否定:
create view negByTT as
select
transactionthrough,
-width as width,
-thickness as thickness
-- and for each other column
from byTT;
然后由您所关心的事务类型总结,用适当的标志:
select sum(width), sum(thickness) from (
select width, thickness
from byTT where transactionthrough = "Purchase"
or transactionthrough = "Return"
union select width, thickness
from negByTT where transactionthrough = "Issue"
or transactionthrough = "Damage"
);
什么是错误? – Minh