Stan Stan - 6 months ago 11
MySQL Question

Mysql issue with group by two columns

The problem:

I have a table with some race results. For each car there are two drivers who each win points individually. To facilitate the work, I created a table with one row for each car (so with the two drivers in two columns: driver1, driver2) instead of one for each driver, because apart from the driver all the other columns have the same value for each car.

Example:

+------+-----+---------+---------+--------+
| race | car | driver1 | driver2 | points | + other variables with same value for both drivers
+------+-----+---------+---------+--------+
| GER | 1 | Michael | Rick | 20 |
| GER | 2 | Steve | Tom | 8 |
| JAP | 1 | Michael | Josh | 20 |
| JAP | 2 | Steve | Tom | 8 |
+------+-----+---------+---------+--------+


As you can see there are more than two drivers for car number 1. So when I want to see the total score for each driver over the two races, this should be the result:


  1. Michael: 40

  2. Rick: 20

  3. Josh: 20

  4. Steve: 16

  5. Tom: 16



But how do I group the score for each driver dealing with two columns of drivers (driver1 and driver2) ? Or do I simply have to change my table and create a row for each driver?

$sql = "SELECT race, driver1, driver2, points FROM `example-table` GROUP BY ………. ORDER BY `points` DESC";

Answer

You could use the union all operator to query the drivers as a single column, and then group by the driver and sum the points:

SELECT   driver, SUM(points)
FROM     (SELECT driver1 AS driver, points FROM example_table
          UNION ALL
          SELECT driver2 AS driver, points FROM example_table) t
GROUP BY driver
ORDER BY 2 DESC