SQL Server存储过程中的SUM（）

Here I am trying to get the data as below (EXPECTED).

在这里，我试图获取如下数据（预期）。

ID | Project | SC   | PO   | INV
---+---------+------+------+-----
1  | test1   | NULL | 1    | NULL
2  | test2   | NULL | NULL | NULL

but as per my stored procedure I am getting the output as

但根据我的存储过程，我得到输出为

ID | Project | SC   | PO | INV
---+---------+------+----+-----
1  | test1   | NULL | 1  | NULL
2  | test2   | NULL | 1  | NULL

Maybe the result from my inner query is displaying the PO recieved for Project test1 as PO test2 too. Which should not happen. Because Project 'test1' is present in both BudgetDetails and Details tables.

也许我的内部查询的结果是将项目test1收到的PO显示为PO test2。哪个不应该发生。因为Project'test1'存在于BudgetDetails和Details表中。

Any help guys to correct the sum() query i have written?

任何帮助家伙纠正我写的sum（）查询？

SELECT 
    BD.ID, BD.Project,
    (SELECT SUM(Amount) AS SC 
     FROM Details (NOLOCK) a 
     INNER JOIN BudgetDetails (NOLOCK) b ON a.Project = b.Project 
     WHERE ProcurementStatus = 'SC' AND a.Project = b.Project) AS SC,
    (SELECT SUM(Amount) as PO 
     FROM Details (NOLOCK) a 
     INNER JOIN BudgetDetails(NOLOCK) b ON a.Project = b.Project 
     WHERE ProcurementStatus = 'PO' AND a.Project = b.Project) AS PO,
    (SELECT SUM(Amount) as INV 
     FROM Details (NOLOCK) a 
     INNER JOIN BudgetDetails(NOLOCK) b ON a.Project = b.Project 
     WHERE ProcurementStatus = 'INV' AND a.Project = b.Project) AS INV
 FROM 
     BudgetDetails (NOLOCK) BD
 WHERE 
     BD.Quarter = @Quarter AND BD.Year = @Year
 GROUP BY 
     BD.ID, BD.Project

1 个解决方案

#1

to fix your query, you will required AND a.Project = BD.Project in your sub-query

要修复您的查询，您需要在子查询中使用AND a.Project = BD.Project

You are better to re-write it using CASE STATEMENT or PIVOT

你最好用CASE STATEMENT或PIVOT重写它

 SELECT 
    BD.ID, BD.Project,
    SUM(CASE WHEN ProcurementStatus = 'SC' THEN Amount END) as SC,
    SUM(CASE WHEN ProcurementStatus = 'PO' THEN Amount END) as PO,
    SUM(CASE WHEN ProcurementStatus = 'INV' THEN Amount END) as INV,

 FROM   
        BudgetDetails (NOLOCK) BD
 LEFT JOIN 
        Details (NOLOCK) D  ON  BD.Project  = D.Project
 WHERE  
        BD.Quarter = @Quarter 
 AND    BD.Year = @Year
 GROUP BY 
        BD.ID, 
        BD.Project

EDIT 1: change from INNER JOIN to LEFT JOIN to reflect @beginner's requirement

编辑1：从INNER JOIN更改为LEFT JOIN以反映@ beginner的要求

#1