SQL条件求和

Question

我试图对Access中的计算进行编码，这涉及到一些值的和以及IF语句的结果。

下面是我正在处理的数据类型的表(来自‘CostBaseQuery’)

CompanyName Year    AssetName  DatapointID  Datapointname                DatapointValue
CompanyA    2011    AssetA     4025         Active operated wells           129
CompanyA    2011    AssetA     4058         API gravity of oil              38.5563255
CompanyA    2011    AssetA     4032         Number of gas lift wells        70
CompanyA    2011    AssetA     4033         Number of wells with ESPs       0
CompanyA    2011    AssetA     4036         Wells with CO2 production       25
CompanyA    2011    AssetA     4035         Wells with H2S production       9
CompanyA    2011    AssetA     4038         Wells with high pressure        0
CompanyA    2011    AssetA     4037         Wells with high temperature     0
CompanyA    2011    AssetA     4034         Wells with scale production     42
CompanyA    2011    AssetA     4039         Wells with wax                  0

计算需要SUM(DatapointValue)，其中DatapointID IN (4032,4033,4035,4036,4037,4038,4039)

加上IF语句返回的值

如果(石油的比重为<=5或>20，使用ActiveOperatedWells值，否则为0)

我尝试使用下面的方法对计算进行编码，但是它会在和的每一次迭代中输出数据，而不是执行和计算，然后再输出IF语句。

SELECT qb1.CompanyName, qb1.AssetName, qb1.Year, 
(SUM(qb1.DatapointValue) 
+ IIF(qb2.DatapointValue>=5, 
       IIF(qb2.DatapointValue<20, qb3.DatapointValue, 0), 0)) 
AS NumberOfWellCompletions

FROM (CostBaseQuery AS qb1 
     INNER JOIN CostBaseQuery AS qb2 
          ON qb1.CompanyYearAssetID=qb2.CompanyYearAssetID) 
     INNER JOIN CostBaseQuery AS qb3 
          ON qb1.CompanyYearAssetID=qb3.CompanyYearAssetID

WHERE qb1.DatapointID IN (4032,4033,3036,4035,4038,4037,4034,4039)
AND qb2.DatapointID=4058 AND qb3.DatapointID=4025

GROUP BY qb1.CompanyName, qb1.AssetName, qb1.Year, 
qb1.DatapointValue, qb2.DatapointValue, qb3.DatapointValue

任何帮助都将是非常感谢的，我希望这比我以前的闲谈更有意义！

Answer 1

SUM(qb1.DatapointValue + IIF(ab2.DatapointValue <=5 AND qb2.DatapointValue > 20, qb3.DatapointValue, 0)) as NumberOfWellCompletions
...

GROUP BY qb1.CompanyName, qb1.AssetName, qb1.Year

你可以简化你的总和(嗯，不清楚你想要和什么，但你可能有括号问题)

您必须清楚是否需要检查<= 5或>=5 (在您的问题中文本和代码之间的区别)。

你必须改变你的小组

Answer 2

我不认为您想按DatapointValue进行分组，因为这些是单独的值，您正在寻找和。

尝试将GROUP BY更改为：

GROUP BY qb1.CompanyName, qb1.AssetName, qb1.Year