How to summarize the results of a selection that returns multiple rows

Question

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

I am trying to assign a 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, internal selection returns a single column. I want var to be assigned the SUM of all the rows that are returned there.

I understand that this can be done using the temp table quite easily. But is this the only way?

Answer 1

Using:

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 

It makes no sense to use GROUP BY jc.JudgingCriteriaIDit if an external query sums everything up anyway.

Answer 2

SELECT Sum(CategoryScore)
FROM ( subquery )

Answer 3

:

select sum(myColumn) from MyTable where MyTableID = 'some value'

( ):

select sum(isnull(myColumn,0)) from MyTable where MyTableID = 'some value'