CODEWITHSUNDEEP
mysqlsum
h3rrmiller
h3rrmiller

Reputation: 125

SUM score columns for each candidate for each event

The query I have so far is 

SELECT callSign,event,rubric,score 
  FROM scores LEFT JOIN candidates 
    ON scores.candidateID=candidates.id 
  WHERE candidateID IN 
    (SELECT id 
      FROM candidates 
    WHERE assessmentID='1321') 
  ORDER BY candidateID,event

which returns a data set like:

callSign    event                 score
V021    Amazing Race Planning       5
V021    Amazing Race Planning       3
V021    Amazing Race Planning       5
V021    Individual Analyst Exam     3
V021    PSA                         5
V021    Team Analyst Exam           5
V021    Team Analyst Exam           5
V021    Team Analyst Exam           3
V021    Team Analyst Exam           5
V022    PSA                         1
V022    Team Tech Exam              5
V022    Team Tech Exam              3

What Im looking for is for each candidate add up the scores from each event so it looks like:

callSign        event              score
V021    Amazing Race Planning       13
V021    Individual Analyst Exam     3
V021    PSA                         5
V021    Team Analyst Exam           18
V022    PSA                         1
V022    Team Tech Exam              8

Im sure I need some sort of combination of SUM() and GROUP BY but I cant seem to figure it out. And then I need to ORDER BY the candidate with the most points in all categories

NOTE: There are multiple candidates

Upvotes: 0

Views: 117

Answers (4)

dnagirl
dnagirl

Reputation: 20456

SELECT callSign,event, SUM(score) 
  FROM scores LEFT JOIN candidates 
    ON scores.candidateID=candidates.id 
  WHERE candidateID IN 
    (SELECT id 
      FROM candidates 
    WHERE assessmentID='1321') 
  GROUP BY event, callSign
  ORDER BY candidateID,event

Usually, the major grouping (in your case event) is the first column. But since your desired result set has multiple rows for the same callSign, it must perforce be the minor grouping column and not the major grouping column.

If you want the major grouping to be for candidate, include the candidate field in your query and also include it in the grouping like this GROUP BY candidateID, event, callSign

Upvotes: 1

Tom
Tom

Reputation: 6663

The other answers are correct also, but I would remove the IN .. subselect statement. It's unnecessary.

Group by the fields you would like to group by, and sum the field you would like to total. 

SELECT callSign, event, SUM(score) score

FROM scores 

     JOIN candidates 
     ON scores.candidateID = candidates.id 

WHERE candidates.assessmentID = '1321'

GROUP BY callSign, event

ORDER BY candidateID, event

Upvotes: 2

Strawberry
Strawberry

Reputation: 33935

Just a guess...

 SELECT callSign
      , event
      , SUM(score) total
   FROM scores s
   LEFT 
   JOIN candidates c
     ON c.id = s.candidateID
    AND c.assessmentID=1321
  GROUP
     BY callsign
      , event;

Upvotes: 0

CathalMF
CathalMF

Reputation: 10055

SELECT callSign,event,rubric,SUM(score) 
  FROM scores LEFT JOIN candidates 
    ON scores.candidateID=candidates.id 
  WHERE candidateID IN 
    (SELECT id 
      FROM candidates 
    WHERE assessmentID='1321') 
GROUP BY callSign,event,rubric
ORDER BY candidateID,event

You basically just need to add every other column that you are not summing to the group by.

EDIT. Sorry put the group by in the wrong place.

Upvotes: 0

Related Questions