SUM with a pivot to calculate overall score

Question

From another question I got this query to get my scores summed up properly:

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

I get data that looks like:

callSign    event           TotalScore
Y209    Bridge                  45
Y209    PSA                     3
Y209    Team Analyst Exam       40
X125    PSA                     1
X125    Team Analyst Exam       38
V023    Amazing Race Planning   37

What I need is data like:

callSign      Bridge   PSA   Amazing Race Planning     Team Analyst Exam   
V023                                37
Y209           45       3                                   40         
X125                    1                                   38        

TABLE STRUCTURE

`events`
id  event
1   PSA
2   Bridge
30  Stress Board
25  Amazing Race Planning
26  Amazing Race Execution

`scores`
id  candidateID     event            rubric            category                         score   comment
1   18       Team Analyst Exam  Team Leader Rubric  Organizes and Tasks Team Members    3    
2   18       Team Analyst Exam  Team Leader Rubric  Roles and Responsibilities          5    
3   18       Team Analyst Exam  Team Leader Rubric  Backward Planning                   5    
4   18       Team Analyst Exam  Team Leader Rubric  Time Management

`candidates`    
id  firstName   middleInitial   lastName    callSign    service     rank    sex     height  weight  assessmentID    currentlyAssessing  hired

callSign is where X125 would go

Answer 1

Since you are using MySQL, in order to pivot the data into columns, you will need to use an aggregate function with a CASE expression:

SELECT callSign, 
    SUM(case when event = 'Bridge' then score else 0 end) as Bridge,
    SUM(case when event = 'PSA' then score else 0 end) as PSA,
    SUM(case when event = 'Amazing Race Planning' then score else 0 end) As AmazingRacePlanning,
    SUM(case when event = 'Team Analyst Exam' then score else 0 end) as TeamAnalystExam
FROM scores 
LEFT JOIN candidates 
    ON scores.candidateID=candidates.id 
WHERE candidateID IN (SELECT id 
                      FROM candidates 
                      WHERE assessmentID='1321') 
GROUP BY callSign

If you have an unknown number of events, then you will have to use a prepared statement to generate dynamic SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN event = ''',
      event,
      ''' THEN score END) AS `',
      event, '`'
    )
  ) INTO @sql
FROM scores 
LEFT JOIN candidates 
    ON scores.candidateID=candidates.id;


SET @sql 
  = CONCAT('SELECT callSign, ', @sql, ' 
           FROM scores 
            LEFT JOIN candidates 
                ON scores.candidateID=candidates.id 
            WHERE candidateID IN (SELECT id 
                                  FROM candidates 
                                  WHERE assessmentID=''1321'') 
            GROUP BY callSign');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Edit #1, if your events are stored in a separate table, then you can use the following to generate the dynamic result:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN event = ''',
      event,
      ''' THEN score END) AS `',
      event, '`'
    )
  ) INTO @sql
FROM events;



SET @sql 
  = CONCAT('SELECT callSign, ', @sql, ' 
           FROM scores 
            LEFT JOIN candidates 
                ON scores.candidateID=candidates.id 
            WHERE candidateID IN (SELECT id 
                                  FROM candidates 
                                  WHERE assessmentID=''1321'') 
            GROUP BY callSign');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo