Thanks much for the responses - they were helpful but was not quite what i wanted. Perhaps I was not clear. So, here goes...
The data is sorted by Longitude and then by Distance. Whenever there are repeated values of Distance within a Longitude, I want the Sum and Count to be added and divided - for example, Here is the table: Longitude Distance AvgColz SumColz CountColz 71.0 10 50.0000 100 2 71.0 10 92.8571 2600 28 71.0 20 83.8710 5200 62 71.0 20 82.6389 11900 144 71.0 30 62.0425 2916 47 71.0 30 81.7119 9642 118 71.0 40 17.8723 840 47 71.0 40 49.8868 2644 53 71.0 50 8.5814 369 43 71.0 60 5.0238 211 42 71.0 70 2.9302 126 43 71.0 80 2.1707 89 41 71.0 90 1.8500 74 40 71.0 100 1.5750 63 40 Notice that the Distance is repeated for the Longitude...I want the repeated Distance rows to be replaced by a weighted average of SumColz and CountColz. So, the output should be: Longitude Distance AvgColz 71.0 10 90.00 71.0 20 83.01 71.0 30 76.11 71.0 40 34.84 71.0 50 8.58 71.0 60 5.02 71.0 70 2.93 71.0 80 2.17 71.0 90 1.85 71.0 100 1.57 Example the 90.00 in the first row above is (100+2600)/(2+28) and so on...there could be multiple repeats in Distance although I have shown only two here... Thanks much!! Prasad On Tue, Aug 16, 2011 at 12:35 PM, Pavel Ivanov <paiva...@gmail.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
