The MYSQL subsection when summing common values ​​is

Question

UPDATE: reformatted the whole question

I have a database with individual results of the Formula 1 race. Each driver receives points for the place in which they end. Now I want to build a table with all the combined points of all the races for each driver. Thus, basically the table is “driver table”.

I can easily get the total points, but the problem that I am encountering occurs when multiple drivers have the same common points. In these cases, I need to check which of the drivers has the best (or more) result of a single race, since it should be higher on the final table.

Oh, and in the placement column “0” means that he didn’t end up with the worst result.

---

A simple example:

Event  | Driver    | Placement | Points
--------------------------------------
Race 1 | Driver A  | 1         | 3
Race 1 | Driver B  | 2         | 2
Race 1 | Driver C  | 3         | 1
Race 2 | Driver A  | 3         | 3
Race 2 | Driver B  | 2         | 2
Race 2 | Driver C  | 1         | 1
Race 3 | Driver A  | 0         | 0
Race 3 | Driver B  | 0         | 0
Race 3 | Driver C  | 2         | 2

Driver    | Points
------------------
Driver C  | 4
Driver A  | 4
Driver B  | 4

- , Driver C . A , , B . (, , .)

http://sqlfiddle.com/#!9/16f9c5

| Driver        | Points |
|------------------------|
| N. Hulkenberg | 43     |
| F. Massa      | 43     |
| P. Wehrlein   |  5     |
| D. Kvyat      |  5     |
| M. Ericsson   |  0     |
| P. Gasly      |  0     |
| A. Giovinazzi |  0     |
| B. Hartley    |  0     |

, . , , , - ( 6- , 2- , ).

:

1. N. Hulkenberg - has four 6th place finishes
2. F. Massa      - has two 6th place finishes
3. P. Wehrlein   - has 8th place finish
4. D. Kvyat      - has 9th place finish
5. M. Ericsson   - has 11th place finish
6. P. Gasly      - has 12th and 13th place finishes
7. A. Giovinazzi - has only one 12th place finish and nothing else
8. B. Hartley    - has 13th place finish

[Hulkenberg vs Massa] [Gasly vs Giovinazzi]

.

Answer 1

.

SELECT     
    driver   
   ,SUM(points) AS points  
   ,CONCAT(GROUP_CONCAT(IF(placement = 0, 99, LPAD(placement,2,0)) ORDER BY IF(placement = 0, 99, placement) SEPARATOR ','), ',99') AS best 
FROM   
   f1 
GROUP BY   
   driver  
ORDER BY     
   points DESC,    
   best

, :

SELECT 
    driver
   ,SUM(points) AS points
FROM
    f1
GROUP BY
    driver
ORDER BY 
    points DESC
   ,CONCAT(GROUP_CONCAT(IF(placement = 0, 99, LPAD(placement,2,0)) ORDER BY IF(placement = 0, 99, placement) SEPARATOR ','), ',99')

- () .

Answer 2

.

select t1.driver, points, min_placement 
from (select driver, sum(points) points from f1 group by driver) t1 inner join
     (select driver, min(placement) min_placement from f1 group by driver) t2 
  on t1.driver = t2.driver
order by points desc, min_placement asc

, .

Answer 3

I assume that you will need at least a second time-break after the highest level:

SELECT
    driver,
    SUM(points) as points,
    CASE WHEN MAX(placement) = 0 THEN 99 ELSE MAX(placement) END AS highest_finish,
    -- or maybe this would work?
    SUM(CASE WHEN placement <> 0
        THEN POWER(20, placement) ELSE 0 END) AS weighted_finish 
FROM f1
GROUP BY driver
ORDER BY points DESC, highest_finish

You may also be lucky with this expression:

SELECT f1.driver,
    MAX((100 - f1.placement) * (
        SELECT count(*) FROM f1 f2
        WHERE f2.driver = f1.driver 
          AND f2.placement = f1.placement
          AND f2.placement > 0
        )) AS highest_placement_weight
FROM f1
GROUP BY f1.driver