SQL query to group records under separate fields

Question

I'm working with MySQL, and I have the following schema:

id school          round score win loss tie
2  My School Name  1     10    1   0    0
3  My School Name  2     20    0   1    0
4  My School Name  3     30    1   0    0
5  My School Name  4     40    1   0    0
6  My School Name  5     50    1   0    0
7  My School Name  6     60    0   0    1

And I need the following output, grouped by school name

School         Round1 Round2 Round3 Round4 Round5 Round6 wins losses ties
My School Name 10     20     30     40     50     60     4    1      1

So far I feel like I can use GROUP BY School and SUM(win) as wins to get most of the functionality out of it. The hard part, though, is to get those Round_ fields.

Does anyone know how to do this? Thanks in advance, any help would be much appreciated!

Edit: to clarify, I know I have 10 rounds exactly.

Answer 1

We can use a SELECT statement with a GROUP BY school to create a record for each school. The ties, wins, and losses columns are readily calculated with the SUM aggregate function, as you noted. To target a specific round, we can use some clever math (to avoid verbose conditional statements like the one CodeByMoonlight suggested):

If we want to target round R, we note that "round-R" is 0 only when round == R, otherwise it isn't 0. When we take the NOT of "round-R", 0 gets inverted to 1, while everything else gets set to 0. Now, if we multiply !(round-R) by the score of that round, it will give us 0 when the round is not R (as 0*score = 0) and it will give us "score" when the round is R (as 1*score = score). Next, when we take the SUM of this value over the columns, we add score when round=R and 0 otherwise, effectively giving us just the round R score.

Putting that all together gives:

SELECT school AS `School`,
SUM(!(round-1)*score) AS `Round1`,
SUM(!(round-2)*score) AS `Round2`,
SUM(!(round-3)*score) AS `Round3`,
SUM(!(round-4)*score) AS `Round4`,
SUM(!(round-5)*score) AS `Round5`,
SUM(!(round-6)*score) AS `Round6`,
SUM(!(round-7)*score) AS `Round7`,
SUM(!(round-8)*score) AS `Round8`,
SUM(!(round-9)*score) AS `Round9`,
SUM(!(round-10)*score) AS `Round10`,
SUM(win) AS `wins`,
SUM(loss) AS `losses`,
SUM(tie) AS `ties`
FROM `RoundScores` GROUP BY `school`

where RoundScores is the table in question.

EDIT:

If we do not want to manually add 10, we can use prepared statements :

# Store all the conditionals in a string:
#   I was not able to to have round loop from 1 to 10, so I iterated over
#   all distinct values of 'round' present in the table.
SET @s =  "";
SELECT `round`, (@s := CONCAT( @s ,  "SUM(!(round-",round,  ")*score) AS `Round",round,  "`," )) FROM `RoundScores` GROUP BY `round`;

# Combine the conditionals from before with the rest of the statement needed.
SET @qry = CONCAT("SELECT school AS `School`,",@s,"SUM(win) AS `wins`,SUM(loss) AS `losses` FROM `RoundScores` GROUP BY `school`");

# Prepare and execute the statement.
PREPARE stmt1 FROM @qry;
EXECUTE stmt1;

Answer 2

TRY WITH UNION ( not tested)

   SELECT SUM(win) AS wins, SUM(loss) AS losses, SUM(tie) AS ties
   FROM table 
   GROUP BY (school)

   UNION

   SELECT score AS round1 FROM table WHERE round=1

   UNION

   SELECT score AS round2 FROM table WHERE round=2
.... AND so on..

Answer 3

SELECT School, Sum(Case
    When Round = 1 Then Score
    Else 0
End) AS Round1, Sum(Case
    When Round = 2 Then Score
    Else 0
End) AS Round2, Sum(Case
    When Round = 3 Then Score
    Else 0
End) AS Round3, Sum(Case
    When Round = 4 Then Score
    Else 0
End) AS Round4, Sum(Case
    When Round = 5 Then Score
    Else 0
End) AS Round5, Sum(Case
    When Round = 6 Then Score
    Else 0
End) AS Round6, Sum(Case
    When Round = 7 Then Score
    Else 0
End) AS Round7, Sum(Case
    When Round = 8 Then Score
    Else 0
End) AS Round8, Sum(Case
    When Round = 9 Then Score
    Else 0
End) AS Round9, Sum(Case
    When Round = 10 Then Score
    Else 0
End) AS Round10, Sum(Wins) AS Wins, Sum(Losses) AS Losses, Sum(Ties) AS Ties
FROM MyTable
GROUP BY School

Should work :)