Case语句未将所有值相加

Question

我使用Case语句来根据产品在建筑物中的位置乘以产品的成本。我的Case语句只会添加来自一条记录的值。我的SQL技能不是很好，所以它可能只是一个简单的语法错误，我找不出来

SELECT ISNULL (SUM(Cost), 0),  
CASE 
 WHEN (Position = 'RC' AND Vinyl != 'N/A') THEN SUM( Cost * .40) 
 WHEN (Position = 'RC' AND Vinyl = 'N/A') THEN SUM( Cost * .75)
 WHEN (Position = 'QW' AND Vinyl != 'N/A') THEN SUM( Cost * .45)
 WHEN (Position = 'QW' AND Vinyl = 'N/A') THEN SUM( Cost * .80)
 WHEN (Position = 'PR' AND Vinyl != 'N/A') THEN SUM( Cost * .80)
   END 
 FROM InternalRemakes
WHERE RemakeDate = DATEADD(day, 0, @Date) GROUP BY Position, Vinyl 

Cost Vinyl   Position
9.43    = N/A RC
9.43 = N/A  RC
10.51   != N/A RC
23.4 != N/A     RC
57.95 != N/A    PR

在我的查询中，在CASE语句之后我只得到了10.51的值。表中的所有记录都应该能够运行CASE语句，然后将总金额相加。

Answer 1

我认为您需要在SUM()中使用CASE语句：

SELECT 
  ISNULL(SUM(Cost), 0),  
  SUM(Cost * 
    CASE 
      WHEN (Position = 'RC' AND Vinyl != 'N/A') THEN .40 
      WHEN (Position = 'RC' AND Vinyl = 'N/A') THEN .75
      WHEN (Position = 'QW' AND Vinyl != 'N/A') THEN .45
      WHEN (Position = 'QW' AND Vinyl = 'N/A') THEN .80
      WHEN (Position = 'PR' AND Vinyl != 'N/A') THEN .80
    END
  ) 
FROM InternalRemakes
WHERE RemakeDate = DATEADD(day, 0, @Date) --<-----What is this?
GROUP BY Position, Vinyl