Well, not easy in a programming language - however, certainly an option. I
was hoping that there would be some kind of break in sequence aggregation
available (or a trick) in Sqlite. My problem I suspect is not so uncommon. I
know that I can do this in INFO (an outdated database of ArcInfo) and I
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 221
43 71.0 10 29.4286 28
You can't replace multiple rows in a single insert/update/delete statement.
You might consider copying the duplicates to a temp table, delete them
from the old then use a select on the temp table to generate the new
rows for the old table. The select portion would be something like
select
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);
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 -
On 16 Aug 2011, at 9:58pm, Anantha Prasad wrote:
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
On 8/16/2011 4:58 PM, Anantha Prasad wrote:
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
select Longitude, Distance, sum(SumColz) / sum(CountColz) as AvgColz
from
