SQLite Forum

Substract Yesterday Cases from today's Cases

Substract Yesterday Cases from today's Cases

(1) By David Jackson (davidjackson) on 2020-12-05 23:40:41 [link] [source]

My table looks like this: Date geoID Cases Deaths. What I'm want to do is substract yesterday's Case from today's Cases. I came across code for LAG() function online. Here is the code I tried to modify

/* Calculate daily totals Source: https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-lag/ */ SELECT geoID, Reported, Cases, LAG ( Cases, 1, 0 ) OVER ( ORDER BY Reported ) PreviousTotalCases FROM ECDC WHERE geoID ="US";

(2) By Keith Medcalf (kmedcalf) on 2020-12-06 03:10:22 in reply to 1 [link] [source]

So what did your result look like, what did you expect to obtain as a result, why do you think you should have got that result, and what results did you obtain instead?

Did you read the documentation on the sqlite3 website for window functions (and in particular the lag function)?

(3) By David Jackson (davidjackson) on 2020-12-06 22:21:10 in reply to 2 [source]

How about something like this:

SELECT date, confirmed, confirmed - LAG ( confirmed, 1, 0 ) OVER ( ORDER BY date ) DailyCases FROM JHUDATA where country = "US"