SQLite Forum

How can I calc weekly totals?
Login

How can I calc weekly totals?

(1) By David Jackson (davidjackson) on 2020-12-04 02:27:29 [link] [source]

My covid table contains one row for each day, Each row contains looks like this: date,cases, deaths. I want to calc the weekly totals for cases and deaths. The query result would have the Sunday YMD, TotalCases and TotaldDeaths. TIA, David

(2) By Keith Medcalf (kmedcalf) on 2020-12-04 13:30:04 in reply to 1 [source]

How about something like:

select enddate,
       sum(...),
       ...
  from (
        select date(cast(julianday(datecolumn)-1721061.5 as integer)/7*7+1721067.5) as enddate,
               *
          from theTable
       )
group by enddate
order by enddate;

(4) By David Jackson (davidjackson) on 2020-12-04 18:27:31 in reply to 2 [link] [source]

Thanks, I give it a try. David

(5) By David Jackson (davidjackson) on 2020-12-04 22:00:13 in reply to 2 [link] [source]

Keith, Thanks that was exactly what I was looking for.

(6) By Keith Medcalf (kmedcalf) on 2020-12-05 00:43:39 in reply to 2 [link] [source]

The constant 1721061.5 is the JulianDay of '0000-01-03' which is the first Monday in the "round trippable" dates supported by the SQLite3 datetime functions.

cast(julianday(datecolumn)-1721061.5 as integer)/7 gives us the Week Number (as an integer) based on a week starting on a Monday (base 0).

You then multiple this by 7 to convert back to days, and add 1721067.5 (which is the JulianDay value corresponding to '0000-01-09'), which is the JulianDay value on which the first week ends, and convert the result back to a Gregorian date.

Thus given any Gregorian date in the Common Era you can now compute the Week Ending date, for a week spanning Monday -> Sunday. You could (for example) return the "starting date" of each week by changing the last constant to 1721061.5.

(3.1) Originally by Ryan Smith (cuz) with edits by Richard Hipp (drh) on 2020-12-04 15:48:01 from 3.0 in reply to 1 [link] [source]

Spam post deleted