SQLITE SUM and LIMIT don't seem to work together

Question

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.

Answer 1

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

Answer 2

Can't you do a subelect?

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