CODEWITHSUNDEEP
mysqlsqlselectgroup-by
Stan
Stan

Reputation: 963

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";

Upvotes: 1

Views: 42

Answers (2)

Mureinik
Mureinik

Reputation: 311308

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

Upvotes: 1

Tin Tran
Tin Tran

Reputation: 6202

SELECT driver, SUM(points) as points
FROM
    (SELECT driver1 as driver, points
    FROM `example-table`
    UNION ALL
    SELECT driver2 as driver, points
    FROM `example-table`)T
GROUP BY driver

Upvotes: 1

Related Questions