Making statistics on database columns through Cursor?

Question

I have a Cursor querying a database. I'm interested in getting all numbers from a column, and make some simple statistics on them (e.g., mean, standard deviation and not so much more) which will be shown to the user.

I miss the whole pattern here. Should I store all the values in, say, a int[] array and then make operations? The only way that comes to my mind is something like:

int entries = cursor.getCount();
int[] array = new int[entries];

for (int i = 0; i < entries; i++) {
    cursor.moveToPosition(i);
    array[i] = cursor.getInt(...);
}

And at this point I'm stuck, because I don't know how to perform stuff (be it even a simple mean) on int[], without maybe a second for.

As a second chance, I could go like:

int entries = cursor.getCount();
float mean = 0;

for (int i = 0; i < entries; i++) {
    cursor.moveToPosition(i);
    int newValue = cursor.getInt(...);
    mean = mean + newValue/entries;
}
return mean;

But it keeps looking bad to me. Moreover, things could get worse if I need to calculate more complex statistics. Could this for loop be avoided somehow? Is there any more correct way?

Answer 1

you can use the function AVG of sqlite to calculate the mean. To calculate the standard deviation, you can use the function SUM, combined with the AVG. As you correctly stated in your comment

SELECT SUM((column - subquery.avg) * (column - subquery.avg)) / (COUNT(column)-1) 
   FROM table, 
    (SELECT AVG(column) AS avg FROM table) AS subquery

should do it