I think it will have better performance if you do that in your programming language. But if you insist on SQL it would look like this:
update table_name set IVmean = (select sum(IVsum)/sum(IVcount) from table_name t where t.Longitude = table_name.Longitude and t.Distance = table_name.Distance); I intentionally made all rows to be updated because adding WHERE condition will most probably only slowdown the update. Pavel On Tue, Aug 16, 2011 at 12:05 PM, Anantha Prasad <fab...@gmail.com> wrote: > Wanted to know if Sqlite can do simple math when there is a break in > sequence in the data. For example the foll. table is sorted by Longitude and > then Distance: > > Id Longitude Distance IVmean IVsum IVcount > 42 71.0 10 10.5000 2 21 > 43 71.0 10 29.4286 28 824 > 44 71.0 20 9.4677 62 587 > 45 71.0 20 11.6667 144 1680 > 46 71.0 30 3.5532 47 167 > 47 71.0 40 4.5732 49 67 > > I want to compute replace the IVmean for each repeated Distance by > IVsum/IVcount - for repeated Distance - for example, > > Record 42 and 44 should be replaced by > 71.0 10 (2+28)/(21+824) > Record 44 and 45 should be replaced by > 71.0 20 (62+144)/(587+1680) > > Is this possible to do this in Sqlite. > > Thanks much. > Pras > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
