Counting changes in timeline with MySQL

mysql

Solution

SET @last_task = 0;
SELECT SUM(new_task) AS tasks_performed
FROM (
  SELECT 
    IF(@last_task = RobotShortestPath, 0, 1) AS new_task,
    @last_task := RobotShortestPath
  FROM table
  ORDER BY ??
) AS tmp

Update for multiple tables From a database strcture normailization view, your better of with one table, and have a filed identifing what column is what robot, if that not posible for some reason, you can get that by union the tables:

SET @last_task = 0;
SELECT robot_id, SUM(new_task) AS tasks_performed
FROM (
  SELECT 
    IF(@last_task = RobotShortestPath, 0, 1) AS new_task,
    @last_task := RobotShortestPath
  FROM (
    SELECT 1 AS robot_id, robot_log_1.* FROM robot_log_1
    UNION SELECT 2, robot_log_2.* FROM robot_log_2
    UNION SELECT 3, robot_log_3.* FROM robot_log_3
    UNION SELECT 4, robot_log_4.* FROM robot_log_4
    UNION SELECT 5, robot_log_5.* FROM robot_log_5
  ) as robot_log
  ORDER BY robot_id, robot_log_id
) AS robot_log_history
GROUP BY robot_id
ORDER BY tasks_performed DESC

Problem

I am new to MySQL and I need your help. I have a table with similar data ``` --------------------------------------------------- |RobotPosX|RobotPosY|RobotPosDir|RobotShortestPath| --------------------------------------------------- |0.1 | 0.2 | 15 | 1456 | |0.2 | 0.3 | 30 | 1456 | |0.54 | 0.67 | 15 | 1456 | |0.68 | 0.98 | 22 | 1234 | |0.36 | 0.65 | 45 | 1234 | |0.65 | 0.57 | 68 | 1456 | |0.65 | 0.57 | 68 | 2556 | |0.79 | 0.86 | 90 | 1456 | --------------------------------------------------- ``` As you can see there are repeated values in the column RobotShortestPath, But they are important. Each number represent a specific task. If the number repeats consecutively(ex: 1456), it means that Robot is performing that task, and when the number changes(ex: 1234) it means that it has switched to another task. And if the previous number(ex:1456) appears again it also means that robot is performing a new task(1456) after done with earlier task(1234). So where I am stuck is I am unable to get no of tasks performed. I have used several things from my minimum knowledge like COUNT, GROUP BY but nothing seem to work. Here the no.of tasks performed are 5 actually, but whatever I do I get only 3 as result.

Original source