Hello
AE I have many different ways of ordering these objects. Usually using
AE several sort parameters. I need a running sum of size that works
AE regardless of what order the objects are in.
I tried creating a UDF, but it seems that ordering is done after the
unordered results have been collected, so the running total column was not
increasing every time, as it should.
There probably is a sub query that would give you a sum of the rows up to
and including each row in the table, depending on different orderings. But
it would be quite slow if you had a lot of rows.
Then I thought of using the UDF on a sorted sub query, e.g.
SELECT my_sum(t2.val)
FROM (SELECT *
FROM table AS t1
ORDER BY t1.col1) AS t2
ORDER BY t2.col1;
where 'col1' is what ever column you want to sort on, and val is the
column to be summed.
Without the outer order by clause, the results were wrong. But now the
table is only sorted twice, rather than for each row in the table.
The UDF is just a function which has access to a variable using the
sqlite3_user_data function. The value of the column being summed is added
to an integer in this variable (a C struct), and then this new value is
returned using sqlite3_result_int.
It looks like this:
/* function */
void my_sum(sqlite3_context *ctxt, int argc, sqlite3_value **argv)
{
aggregate_ctxt *agg_ctxt;
agg_ctxt = (aggregate_ctxt *) sqlite3_user_data(ctxt);
agg_ctxt->total += sqlite3_value_int(argv[0]);
sqlite3_result_int(ctxt, agg_ctxt->total);
}
But this would be more easily done once the ordered results were in a 2D
array, i.e. outside of SQL/sqlite.
Swithun.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
