CODEWITHSUNDEEP
mysqlpivot-table
Kyle V.
Kyle V.

Reputation: 4792

How to make a query to pivot/aggregate like this?

How would I query this data set:

SessionId | GameMode | Score
----------|----------|-------
1         | 1        | 100
1         | 1        | 90
1         | 2        | 20
1         | 2        | 15
1         | 3        | 5
1         | 3        | 5
2         | 1        | 90
2         | 1        | 80
2         | 2        | 15
2         | 2        | 15
2         | 3        | 2
2         | 3        | 4

to turn it into:

SessionId | GameMode1AvgScore  | GameMode2AvgScore | GameMode3AvgScore
----------|--------------------|-------------------|-------------------
1         | 95                 | 17.5              | 5
2         | 85                 | 15                | 3

To clarify, I'm not asking how to group or aggregate rows, the core of my question is rather how do I pivot the GameMode rows into a column?

Upvotes: 0

Views: 538

Answers (1)

spencer7593
spencer7593

Reputation: 108480

With MySQL, to return the specified resultset, we would need a query (SELECT statement) that returns four columns.

 SELECT expr1    AS SessionId
      , expr2    AS GameMode1AvgScore
      , expr3    AS GameMode2AvgScore
      , expr4    AS GameMode3AvgScore
 FROM ... 
GROUP BY SessionId

MySQL does not yet provide "PIVOT" (ala SQL Server and PostgreSQL) or "MODEL" (ala Oracle) features.

So in terms of achieving this result from a SQL SELECT statement, we are left with old-school conditional aggregation, 

 AVG(CASE WHEN t.GameMode = 1 THEN t.Score ELSE NULL END) AS GameMode1AvgScore

which seems to fall into the "how to group or aggregate rows" bucket you are not asking about. We can use a second SQL statement to help us build the SQL statement we need to run, but seems like that would still fall into the "how to group or aggregate rows". In fact, any SQL statement that produces the specified result is going to need to aggregate rows.

So with that tool excluded from our MySQL toolbelts, that leaves us with one answer: it is not possible to "pivot" GamerMode values into columns.

A statement to return the specified result might be like this:

 SELECT t.sessionid                                              AS SessionId
      , AVG(CASE WHEN t.GameMode = 1 THEN t.Score ELSE NULL END) AS GameMode1AvgScore
      , AVG(CASE WHEN t.GameMode = 2 THEN t.Score ELSE NULL END) AS GameMode2AvgScore
      , AVG(CASE WHEN t.GameMode = 3 THEN t.Score ELSE NULL END) AS GameMode3AvgScore
    --                             ^                                        ^
 FROM t
GROUP BY t.sessionid
ORDER BY t.sessionid

Note how expressions to return the average scores are the same pattern, we're just substutiting in a value of GameMode.

To help us write the query, we could run an initial query to get a distinct list of GameMode values, either from the same table

SELECT q.GameMode FROM t q GROUP BY q.GameMode ORDER BY q.GameMode

or we could query a "dimension" table of GameMode if that exists.

We cycle through the resultset (GameMode values) and generate the expression required to return the average score for each GameMode. And we can use that to construct the SQL statement.

Upvotes: 2

Related Questions