How do I sum the results of a select that returns multiple rows

Question

I have a SQL Var @SumScore dec(9,4)

I am trying to assign the variable as follows:

SET @SumScore =   
     (  
      SELECT Sum(  
       (  
        SELECT SUM(etjs.CalculatedScore * sc.PercentOfTotal) as CategoryScore   
        FROM tblEventTurnJudgeScores etjs 
         INNER JOIN tblJudgingCriteria jc ON  jc.JudgingCriteriaID = etjs.JudgingCriteriaID  
         INNER JOIN tblScoringCategories sc ON jc.ScoringCategoryID = sc.ScoringCategoryID  
        GROUP BY jc.JudgingCriteriaID  
       ) 
      As ComputedScore) AS SumTotalScore  
     )

In other words...the inner select is returning one column. I want the var to be assigned the SUM of all of the rows that are being return there.

I realize that this could be done with a temp table pretty easily. But is that the only way?

Thanks for your help.

Seth

Answer 1

SELECT Sum(CategoryScore)
FROM ( subquery )

Answer 2

Use:

SET @SumScore = SELECT SUM(etjs.CalculatedScore * sc.PercentOfTotal) as CategoryScore   
                  FROM tblEventTurnJudgeScores etjs 
                  JOIN tblJudgingCriteria jc ON  jc.JudgingCriteriaID = etjs.JudgingCriteriaID  
                  JOIN tblScoringCategories sc ON jc.ScoringCategoryID = sc.ScoringCategoryID

There's no point to using GROUP BY jc.JudgingCriteriaID if the outer query is going to sum up everything anyway.