select 單別aa,單號bb,sum(生產數量dd) as 生產數量加總,sum(報廢數量ee) as 報廢數量加總
from yourTable
where 單別aa='123' and 單號bb='456'
group by 單別aa,單號bb
select 單別aa,單號bb,sum(生產數量dd) 生產數量dd ,sum(報廢數量ee) 報廢數量ee
from TABLE_NAME
group by 單別aa,單號bb
TABLE NAME 就是這個TABLE 的名稱
我是這樣寫
SELECT SUM(CASE WHEN aa = '123' and bb = '456' THEN dd+ee ELSE 0 END) total FROM sum_num;
給你參考
將小計入列
SELECT DocumentNo,DateOrdered, ProductName, MovementDate, DocTypeName, BPartnerName, ProductionQty
FROM (
SELECT o.DocumentNo,1 AS Seq, o.DateOrdered, p.Name ProductName,a.MovementDate,dt.Name DocTypeName,bp.Name BPartnerName, a.ProductionQty
FROM M_Production a
INNER JOIN C_OrderLine l ON a.C_OrderLine_ID=l.C_OrderLine_ID
INNER JOIN C_Order o ON o.C_Order_ID=l.C_Order_ID
INNER JOIN M_Product p ON l.M_Product_ID=p.M_Product_ID
INNER JOIN C_BPartner bp ON o.C_BPartner_ID=bp.C_BPartner_ID
INNER JOIN C_DocType dt ON o.C_DocType_ID=dt.C_DocType_ID
UNION
SELECT DocumentNo,2 AS Seq, null AS DateOrdered, cast('訂單編號小計' as nvarchar2(20)) AS ProductName,null AS MovementDate,null AS DocTypeName,null AS BPartnerName,SUM(ProductionQty)
FROM (
SELECT o.DocumentNo,o.DateOrdered, p.Name ProductName,a.MovementDate,dt.Name DocTypeName,bp.Name BPartnerName, a.ProductionQty
FROM M_Production a
INNER JOIN C_OrderLine l ON a.C_OrderLine_ID=l.C_OrderLine_ID
INNER JOIN C_Order o ON o.C_Order_ID=l.C_Order_ID
INNER JOIN M_Product p ON l.M_Product_ID=p.M_Product_ID
INNER JOIN C_BPartner bp ON o.C_BPartner_ID=bp.C_BPartner_ID
INNER JOIN C_DocType dt ON o.C_DocType_ID=dt.C_DocType_ID
) x
GROUP BY x.DocumentNo
)
ORDER BY DocumentNo, Seq