Very interesting. Thank you David. I have never used a recursive CTE before, so I am still trying to understand exactly how it works. But it does work, so I will definitely take a closer look. Thank you.
Balaji Ramanathan On Tue, Nov 14, 2017 at 6:00 AM, < sqlite-users-requ...@mailinglists.sqlite.org> wrote: > ---------- Forwarded message ---------- > From: David Raymond <david.raym...@tomtom.com> > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Cc: > Bcc: > Date: Mon, 13 Nov 2017 17:20:58 +0000 > Subject: Re: [sqlite] Running sums and averages > As other folks have mentioned, doing it in an external language is going > to be easiest. > > That being said, CTE's are almost a full language in themselves. > > I'm just gonna go with 1 value here for the example, but how about > something like... > > with recursive foo (RowNumber, F1, sumF1, OverallAvgF1, avgF1, F1notNull) > as( > select > rowNumber, > F1, > ifnull(F1, 0.0), > ifnull(F1, 0.0), > ifnull(F1, 0.0), > F1 is not null > from mytable where RowNumber = 1 > > union all > > select > mytable.RowNumber, > mytable.F1, > foo.sumF1 + ifnull(mytable.F1, 0.0), > (foo.sumF1 + ifnull(mytable.F1, 0.0)) / mytable.RowNumber, > (foo.sumF1 + ifnull(mytable.F1, 0.0)) / (foo.F1notNull + (mytable.F1 is > not null)), > foo.F1notNull + (mytable.F1 is not null) > > from > > foo inner join mytable on mytable.RowNumber = foo.RowNumber + 1) > > select RowNumber, F1, sumF1, OverallAvgF1, avgF1 from foo; > > > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
