1

I have a table that records the data of a car race with the following structure:

ID int, (id of car)
Duration timestamp, (time taken to drive)
Tour int,(no of laps made)
Pilot int,(drivers)

where Duration is in time and tour=no of laps which consists of values from 0 to 3000 and pilot has two values 2,3 (2 drivers with id 2 and 3) The table shows the no of laps done within given time for each pilot. The pilots drive the car within the time.Assume pilot 2 has drove the car first next pilot 3 and so on..

Now,I want total tour(difference) and total duree(difference) done by pilot having an id value 2 ?

SAMPLE DATA:

ID|DURATION|TOUR| PILOT
1 |15:00:01|216 |  2 \  
2 |15:00:02|216 |  2 /   1 sec.
3 |15:00:03|217 |  7
4 |15:00:04|218 |  7
5 |15:00:05|219 |  7
6 |15:00:06|220 |  2 \
7 |15:00:07|221 |  2 /   1 sec.
8 |15:00:08|222 |  7
9 |15:00:09|222 |  7
10|15:00:10|223 |  2 \
11|15:00:11|224 |  2  |  
12|15:00:12|225 |  2 /   2 sec.

Now I need the desired result:

For pilot id = 2, I need the tour difference along with duree(total)

Ans: 219 i.e 216 + (221-220) + (225-223)

First tour ID: 216 + (Dif of laps of each group) = 216 + 1 + 2 = 219

And duree(total) = Sum(Dif time of each group) = 00:00:04

1

I've set up the next sample on rextester:

DROP TABLE IF EXISTS race;
CREATE TABLE IF NOT EXISTS race (ID int, DURATION time, TOUR int, PILOT int);
INSERT INTO race VALUES (1 , '15:00:01', 216, 2);
INSERT INTO race VALUES (2 , '15:00:02', 216, 2);
INSERT INTO race VALUES (3 , '15:00:03', 217, 7);
INSERT INTO race VALUES (4 , '15:00:04', 218, 7);
INSERT INTO race VALUES (5 , '15:00:05', 219, 7);
INSERT INTO race VALUES (6 , '15:00:06', 220, 2);
INSERT INTO race VALUES (7 , '15:00:07', 221, 2);
INSERT INTO race VALUES (8 , '15:00:08', 222, 7);
INSERT INTO race VALUES (9 , '15:00:09', 222, 7);
INSERT INTO race VALUES (10, '15:00:10', 223, 2);
INSERT INTO race VALUES (11, '15:00:11', 224, 2);
INSERT INTO race VALUES (12, '15:00:12', 225, 2);

First it flags involved groups:

| ID | DURATION | TOUR | PILOT | GRP |
|----|----------|------|-------|-----|
| 1  | 15:00:01 | 216  | 2     | 1   |
| 2  | 15:00:02 | 216  | 2     | 1   |
| 6  | 15:00:06 | 220  | 2     | 2   |
| 7  | 15:00:07 | 221  | 2     | 2   |
| 10 | 15:00:10 | 223  | 2     | 3   |
| 11 | 15:00:11 | 224  | 2     | 3   |
| 12 | 15:00:12 | 225  | 2     | 3   |

Then calculates time difference of each group:

| DUREE | DIF_TOUR | MIN_TOUR |
|-------|----------|----------|
| 1     | 0        | 216      |
| 1     | 1        | 220      |
| 2     | 2        | 223      |

And finally returns aggregated values:

SELECT SUM(DUREE) DUREE, MIN(MIN_TOUR) + SUM(DIF_TOUR) TOURS
FROM
     (SELECT MAX(DURATION) - MIN(DURATION) AS DUREE, 
             MAX(TOUR) - MIN(TOUR) AS DIF_TOUR, 
             MIN(TOUR) AS MIN_TOUR
      FROM (SELECT ID, DURATION, TOUR, PILOT, 
                   CASE WHEN TOUR <> @LTOUR AND TOUR <> @LTOUR + 1 
                        THEN @GRP := @GRP + 1 ELSE @GRP END AS GRP,
                   @LTOUR := TOUR
            FROM   (SELECT @GRP := 0, @LTOUR := 0) X,
                   (SELECT ID, DURATION, TOUR, PILOT 
                    FROM race 
                    WHERE PILOT = 2 
                    ORDER BY PILOT, TOUR) Y) Z
      GROUP BY GRP) W;

| DUREE | TOURS |
|-------|-------|
| 4     | 219   |

Rextester here

-1

Duration have to be called timestamp.

SELECT MAX(duration) - MIN(duration) AS timediff
     , MAX(tour) - MIN(tour) AS tourdiff
  FROM table
 WHERE pilot = 2
 GROUP BY pilot
;
  • 1
    This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review – Michael Green Jun 26 '17 at 9:54
  • But it does. I'm sure it is some kind of slot-car racing where table is filled by checkpoint data stopwatch/counter. And query perform exactly what has been asked. – Kondybas Jun 26 '17 at 11:07
  • 1
    Thanks for your reply. But ,I'm not getting the desired result as mentioned above. I'm getting the result as duree = 00:00:11 and tour =9 (I dont want this answer) – Uday Bora Jun 26 '17 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.