CODEWITHSUNDEEP
androidsqlsqliteandroid-sqlite
pixelscreen
pixelscreen

Reputation: 1975

SQLITE SUM and LIMIT don't seem to work together

I'm trying to retrieve the sum of a column from SQLITE. I am able to successfully get it. But when I try to retrieve just the sum of 10 rows, it returns the sum of the entire column again. The query seems to be correct though.

public String getUnitsForWeek(Context context) throws IOException {
    DataBaseHelper dbHelper = new DataBaseHelper(context);

    String query = "SELECT sum(UNITS) FROM SERVICE_TABLE order by id DESC limit 7";
    return String.valueOf(dbHelper.getString(query));

}

The dbHelper.getString method is:

public int getString(String query) {
    String mypath = DB_PATH + DB_NAME;
    SQLiteDatabase database = SQLiteDatabase.openDatabase(mypath, null, 
            SQLiteDatabase.OPEN_READWRITE);
    Cursor cursor = null;

    int i;

    cursor = database.rawQuery(query, null);


    cursor.moveToFirst();
    i= cursor.getInt(cursor.getColumnIndex(cursor.getColumnName(0)));
    return i;

}

Thanks.

Upvotes: 3

Views: 1545

Answers (2)

laalto
laalto

Reputation: 152827

SUM is an aggregate function that combines data from many rows into one. Since there is only one result row, LIMIT and ORDER BY are meaningless.

To sum UNITS on the 7 rows with highest ID, you can use a subselect:

SELECT SUM(UNITS) FROM (SELECT UNITS FROM SERVICE_TABLE ORDER BY id DESC LIMIT 7);

Upvotes: 10

Pieter B
Pieter B

Reputation: 1967

Can't you do a subelect?

SELECT sum(UNITS) FROM (SELECT UNITS FROM SERVICE_TABLE order by id DESC limit 7) s

Upvotes: 3

Related Questions