Calculate total score in SQL query from multiple tables

I have the following tables for a competition:

User:

• Id
• Name
• Email
• EntryId

Entry:

• Id
• Age
• Gender
• State

GameResult:

• Id
• EntryId
• Duration
• Score

QuestionResult:

• Id
• EntryId
• Correct
• UsersAnswer

Each entry will have multiple games, and multiple questions. I need to perform a query that will find a list of the highest scores, and then also break down highest score by demographic, i.e. Age, Gender, State.

The calculations are as follows:

Each correct question will have a score value assigned, e.g. 10 points for each correct answer. Each game will have the score already defined in it开发者_StackOverflow社区s column.

So, for an entry, the total score will be:

(Count(Qn.Correct) * QuestionScore) + SUM(G1.Score, G2.Score, Gn.Score)

Not sure where to start in figuring this query out.

note sure individual answer score would be relevant here since you already have final score on the GameResult table.

select e.Age, max(gr.Score) as Score
from Entry e
inner join GameResult gr on(gr.EntryID=e.EntryID)
group by e.Age

then repeat the same thing for the gender and state.

edit: ok, I am not sure I am following your high level design here, but whatever... You need to group your results by the entry ID first, and then by Age/Gender/State. An extra complexity level, but otherwise exactly the same task.

with GameResultScore as (
select EntryID, sum(Score) as Score
from GameResult
group by EntryID
),
QuestionResultScore as (
select EntryID, count(*) CorrectAnswers
from QuestionResult
where Correct=1
group by EntryID
)

select e.Age, max(isnull(grs.Score,0)+isnull(qrs.CorrectAnswers,0) * QuestionScore) as Score
from Entry e
left join GameResultScore  grs on(grs.EntryID=e.EntryID)
left join QuestionResultScore qrs on(qrs.EntryID=e.EntryID)
group by e.Age