How to find actual count from a rolling count in MySQL DB

Question

I have a table where the count of a specific data goes up to 5 and then rolls over to 0, I would like to query the table which shows the actual count as shown below

Desired OutPut:

Dev_count	Actual_Count
1	1
2	2
3	3
4	4
5	5
0	6
1	7
2	8
3	9
4	10
5	11
0	12

I tried using a Trigger to update an additional column with actual count using below SQL script

CREATE TRIGGER insert_continuous_count BEFORE INSERT ON x_table
FOR EACH ROW SET
NEW.continuous_count = (SELECT continuous_count 
  FROM x_table 
  where device_id = NEW.device_id 
  order by No DESC LIMIT 1
)  + 1;

Then I read somewhere that the trigger cannot update the same table which is already being used (for reading or writing) by the statement that invoked the function or trigger.

Is there any easier way to get the desired output?

user_16461952 · Answer

I think you need something like this :

select dev_count, 
       @row_number:=  @row_number + 1 as  Actual_Count
from test_tbl
cross join (select @row_number := 0) x;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/139

In MySQL 8+ you can use ROW_NUMBER function:

SELECT
    dev_count,
    ROW_NUMBER() OVER () as rn
FROM test_tbl
ORDER BY rn;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/144

How to find actual count from a rolling count in MySQL DB

1 Answers