CODEWITHSUNDEEP
javaandroiddatabasesqlitesum
user3197786
user3197786

Reputation: 89

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

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.

Upvotes: 8

Views: 8896

Answers (2)

Agi Hammerthief
Agi Hammerthief

Reputation: 2134

Having looked online, it seems that you can do this.

SELECT food, taxi, clothes, ..., SUM(food + taxi + clothes + ...) AS 'myTotal' FROM table

Source: http://www.codingforums.com/mysql/52471-possible-sum-values-multiple-fields-single-sql-record.html#post275266

Upvotes: 1

Phantômaxx
Phantômaxx

Reputation: 38098

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"));

Upvotes: 6

Related Questions