Android: How to sum all the columns in a SQLite database and return 1 single variable

Question

My objective is to sum all the columns in my database and return a variable 'total' = x amount.

Example:

column - food | taxi | clothes | sports 
          2       3      4         5
          6       8      0         12
          11      10     7         2

Then therefore the total will equate to = 70

My original idea was to sum individual columns and finally add them together example of summing an individual column:

public Cursor sumFoodColumn(){
    Cursor c = db.query(true, DATABASE_TABLE, ALL_KEYS, KEY_FOOD + "=?", new String[]{"sum(food)" + KEY_FOOD}, null, null, null, null);
    if (c !=null) {
        c.moveToFirst();
    }
    return c;
}

But I felt this would be a bit laborious as I've got 13 columns in my database, is there a better way around this? Preferably with only 1 query.

Answer 1

You can run this query:

Cursor c = db.rawQuery("SELECT Sum(food) + Sum(taxi) + Sum(clothes) + Sum(sports) AS myTotal FROM " + DATABASE_TABLE, null);

Instead of

Cursor c = db.query(true, DATABASE_TABLE, ALL_KEYS, KEY_FOOD + "=?", new String[]{"sum(food)" + KEY_FOOD}, null, null, null, null);

and get your total as

Cursor c = sumFoodColumn();
int total = c.getInt(c.getColumnIndex("myTotal"));