Skip to content
Advertisement

MySQL Get Change From Cumulative Results in Consecutive Rows by Identifier

I am running MySQL Community Server version 8.0.19.

I have been struggling with the following problem whilst working through publicly available COVID19 data. I am using a dataset that is both reliable and of good quality, however the data (total_confirmed) is reported using cumulative totals instead of daily counts of infections:

My requirement is to have both the cumulative count and the daily new cases. There is an excellent solution for doing this here and it works like a charm on my dataset provided that I focus on one country only (I used a table populated with Afghanistan data only in this example):

Output:

However, the minute more than one country_region exists in the data, it completely fails and I do not know SQL quite well enough to figure out what I need to change.

Desired Output:

Any assistance would be greatly appreciated. Obviously in a real-world dataset the new_cases values wouldn’t be 0 on 2020-04-05, but in this sample dataset that would be correct.

Advertisement

Answer

If you are running MySQL 8.0, you can do this with window function lag():

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement