Summ up between events SELECT
  A.date as timestamp,
  A.driveNumber as 'Drive Number',
  SUM(2 * 6374.612 * asin(sqrt(POW(sin(radians((B.latitude - A.latitude) / 2)),2) + cos(radians(A.latitude)) * cos(radians(B.latitude)) * POW(sin(radians((B.longitude - A.longitude) / 2)),2)))) as KmDriven
FROM teslafiData A
JOIN teslafiData B ON (A.ta_id = B.ta_id + 1)
WHERE A.latitude &lt;&gt; '' and A.longitude &lt;&gt; '' and B.latitude &lt;&gt; '' and B.longitude &lt;&gt; ''
GROUP By A.driveNumber  
ORDER BY KmDriven DESC


This gives me KmDriven for each driving event. Each drive has its own driveNumber.

Now I need to get km drive between each charging event. Between charges there might be several driving events. Each charge is listed by chargeNumber and while charging the driveNumberis 0 and chargeNumber is counting up for each new charge.

Data Example in the DB:

ta_id    latitude      longitude  driveNumber   chargeNumber
1        47.xxx        8.xxx      1             0
2        47.xxx        8.xxx      1             0
3        47.xxx        8.xxx      1             0
3        47.xxx        8.xxx      1             0
...
80       47.xxx        8.xxx      2             0
81       47.xxx        8.xxx      2             0
82       47.xxx        8.xxx      2             0
...
151      47.xxx        8.xxx      2             0
152      47.xxx        8.xxx      0             1
153      47.xxx        8.xxx      0             1
154      47.xxx        8.xxx      0             1
...
256      47.xxx        8.xxx      0             1
257      47.xxx        8.xxx      3             0
258      47.xxx        8.xxx      3             0
259      47.xxx        8.xxx      3             0


EDIT:

I now managed to get the ta_id when a charging session ends and when it starts again. Therefore I need now to figure out how to calculate the distance between those two events

SELECT ta_id, chargeNumber
FROM (
SELECT ta_id, chargeNumber,  
       IF ( @prev_ver &lt;&gt; chargeNumber, 
           IF (@prev_ver := chargeNumber, 1, 1),
           IF (@prev_ver := chargeNumber, 0, 0)) AS IsDifferent
FROM teslafiData
CROSS JOIN (SELECT @prev_ver := '-1') AS var
WHERE battery_range &lt;&gt; 0 and latitude &lt;&gt; '' and longitude &lt;&gt; '' and latitude &lt;&gt; '' and longitude &lt;&gt; '' 
ORDER BY ta_id ) A
WHERE A.IsDifferent = 1
ORDER BY ta_id ASC
Don't use '' to indicate missing lat/lng -- use NULL instead.  Meanwhile keep lat/lng in, say FLOAT.  See: http://mysql.rjweb.org/doc.php/latlng#representation_choices

If you are using MySQL 8.0 or Mariadb 10, use LEAD(1) and OVER ( PARTITION BY chargeNumber ORDER BY tag_id ) to get the calculate drive distances and the first lat-lng per charge.  In the absence of that windowing function, it would be better to do the arithmetic in your application.


Use LEAD(1) to find the distance for each distance.
SUM() with GROUP BY chargeNumber to get the total distance for each charge.


Be aware that the drive-distance falsely assumes a straight line.