How to you WEIGHT returned values by a Group By value

Question

I am trying to determine a SCORE from 11 rows in a table.

Those 11 rows are being aggregated into five rows using a ScoringCategoryID column as follows...

ScoringCategoryID   CategoryScore       PercentOfTotal
---------------------------------------------------------
7                   15.00             0.40  
8                   15.00             0.30  
9                   14.50             0.20  
10                  4.50             0.05  
11                  4.50             0.05

I need to get a RawScore from this data. Unfortunately my customer does not want me to merely sum the CategoryScore column (53.5 total). Do you see how the PercentOfTotal column sums to 1. Each ScoringCategoryID therefore has a WEIGHT. So of the total score...ScoringCategoryID is supposed to be 40% of the score. ScorCatID 8 is supposed to be 30% of the total etc.

I am not sure how to do this in a query. How do I get the Score?

Here is the current query...

SELECT jc.ScoringCategoryID, 
       SUM(etjs.CalculatedScore) as CategoryScore,  
       Max(sc.PercentOfTotal) PercentOfTotalScore  
  FROM tblEventTurnJudgeScores etjs  
INNER JOIN tblJudgingCriteria jc ON  jc.JudgingCriteriaID = etjs.JudgingCriteriaID  
INNER JOIN tblScoringCategories sc ON jc.ScoringCategoryID = sc.ScoringCategoryID  
WHERE etjs.EventTurnJudgeID = 1068  
GROUP BY jc.ScoringCategoryID

This is supposed to be aggregated into a single score. Any ideas.

Answer 1

SELECT SUM(CategoryScore * PercentOfTotal) as RawScore
  FROM ( .....subquery here..... )

Answer 2

I think we need more information to help you. What is the maximum value that any Category Score can be or does that change per the category?