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
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 my_t
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 SumCo
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 - f
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
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 longit
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
7 matches
Mail list logo