CODEWITHSUNDEEP
androiddatabasesqlite
Kevik
Kevik

Reputation: 9351

how to get the sum of all numbers in a column in a database table

I have the following function and am trying to get all the integers in a column and summing them up for a total.

This code below is not working because for a test I put two rows in the table and the KEY_HITS column has the numbers 5 and 6, the total should be 11 but I am getting 4 as the result

 public int getTotal() {

    String[] columns = new String[] { KEY_ROWID, KEY_NAME, KEY_HITS };
    Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null,
            null, null);
    int total = 0;

    int iHits = c.getColumnIndex(KEY_HITS);

    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
        total = total + iHits;
    }
    return total;
}

Upvotes: 0

Views: 7175

Answers (3)

Rushabh Patel
Rushabh Patel

Reputation: 3080

You can direct make sqlite query like:

Cursor cursor = sqliteDatabase.rawQuery(
    "SELECT SUM(COL_VALUES) FROM myTable", null);
if(cursor.moveToFirst()) {
    return cursor.getInt(0);
}

Hope it will help you.

Upvotes: 7

Shraddha Shravagi
Shraddha Shravagi

Reputation: 1136

getColumnIndex Returns the zero-based index for the given column name, or -1 if the column doesn't exist. You should have c.getInt(KEY_HITS)

Upvotes: 1

GeorgeVremescu
GeorgeVremescu

Reputation: 1253

That is because you're adding the column index twice. You should write

total= total + c.getLong(KEY_HITS);

Upvotes: 1

Related Questions