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 [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 [link] [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;

(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

(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.