CODEWITHSUNDEEP
mysqlsqldatabase
stonewareslord
stonewareslord

Reputation: 353

SQL consolidate data and turn them into columns

So here's what my data table looks like: 

TeamNum Round   Points1   Points2
1       1       5         21
2       1       10        20
3       1       9         29
1       2       6         22
2       2       11        21
3       2       10        30
1       3       80        50

I also have a second table with this: 

TeamNum TeamName
1       goteam1
2       goteam2
3       goteam4-1

I want SQL to take it and turn it into this: 

Team    Round1                      Round2            Round3            TeamName
1       (points1+points2 of round1) (same but for r2) (same but for r3) goteam1
2       (points1+points2 of round1) (same but for r2) (same but for r3) goteam2
3       (points1+points2 of round1) (same but for r2) (same but for r3) goteam4-1

And a sample output from the tables above would be: 

Team    Round1   Round2    Round3    TeamName
1       26       28        130       goteam1
2       30       32        0         goteam2
3       38       40        0         goteam4-1

The actual data has a bunch of "points1" and "points2" columns, but there are only 3 rounds.

I am very new to SQL and this is all I have right now: 

select 
  `data`.`round`,
  `data`.`teamNumber`,
  sum(`Points1`) + sum(`Points2`) as score
from `data` join `teams` ON `teams`.`teamNumber` = `data`.`teamNumber`
group by `data`.`teamNumber` , `round`
order by `data`.`teamNumber`, `data`.`round`

But it doesn't return anything at all. If I remove the join statement, it shows everything like I want, but doesn't consolidate Round1, 2, and 3 as columns, they are each separate rows. Can you guys help me out? Thanks!

Upvotes: 0

Views: 67

Answers (3)

Mihai
Mihai

Reputation: 26784

Something like this:

 select teams.teamNumber,
  SUM(CASE WHEN Round=1 THEN `Points1`+`Points2`  ELSE 0 END)as Round1,
  SUM(CASE WHEN Round=2 THEN `Points1`+`Points2`  ELSE 0 END)as Round2,
  SUM(CASE WHEN Round=3 THEN `Points1`+`Points2`  ELSE 0 END)as Round3,
  teams.teamName
from `data` join `teams` ON `teams`.`teamNumber` = `data`.`teamNumber`
group by teamnumber , teamname
order by `data`.`teamNumber`, `data`.`round`

Upvotes: 0

pobrelkey
pobrelkey

Reputation: 5973

No need to aggregate:

SELECT
    t.teamnumber,
    COALESCE(r1.points1 + r1.points2, 0) AS round1,
    COALESCE(r2.points1 + r2.points2, 0) AS round2,
    COALESCE(r3.points1 + r3.points2, 0) AS round3,
    t.teamname
FROM teams t
LEFT JOIN data r1 ON r1.teamnumber = t.teamnumber AND r1.round = 1
LEFT JOIN data r2 ON r2.teamnumber = t.teamnumber AND r2.round = 2
LEFT JOIN data r3 ON r3.teamnumber = t.teamnumber AND r3.round = 3

Upvotes: 1

peterm
peterm

Reputation: 92805

Use conditional aggregation

SELECT t.teamnumber, t.teamname,
       SUM(CASE WHEN d.round = 1 THEN d.points1 + d.points2 ELSE 0 END) round1,
       SUM(CASE WHEN d.round = 2 THEN d.points1 + d.points2 ELSE 0 END) round2,
       SUM(CASE WHEN d.round = 3 THEN d.points1 + d.points2 ELSE 0 END) round3
  FROM data d JOIN teams t
    ON d.teamnumber = t.teamnumber
 GROUP BY t.teamnumber, t.teamname

Output:

| TEAMNUMBER |  TEAMNAME | ROUND1 | ROUND2 | ROUND3 |
|------------|-----------|--------|--------|--------|
|          1 |   goteam1 |     26 |     28 |    130 |
|          2 |   goteam2 |     30 |     32 |      0 |
|          3 | goteam4-1 |     38 |     40 |      0 |

Here is SQLFiddle demo

Upvotes: 1

Related Questions