SQL分组依据计算

Question

提前谢谢..

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

我使用上面的代码来获取字段'nos‘和'areasqft’的总和。

在“transactionthrough”列中(请参见下图)

我已经输入了‘购买’，‘发行’，‘退货’和‘损坏’作为数据。

实际上，我只需要Sum(nos) - Issue + Return - Damage这样的值，并在查询中使用相同的Group by字段，而不是购买和Sum(areasqft)。

Answer 1

您应该能够在sum中使用下面的if语句对Issue和Damage的值进行求反。

    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

Answer 2

首先，按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" 
);