SQLite: select from column by month

I am trying to sum a tablecolumn on basis of month but I got the following exception:

   java.lang.IllegalStateException: Couldn't read row 0, col -1 from CursorWindow.  Make sure the Cursor is initialized correctly before accessing data from it.

the function

public static float test(SQLiteDatabase db) {

    String expenditure = MyDatabaseContract.TableExpenditure.ATTR_EXPENDITURE;
    String table = MyDatabaseContract.TableExpenditure.TABLE_EXPENDITURE;
    String date = MyDatabaseContract.TableExpenditure.ATTR_DATE;

    String query = "SELECT SUM(" + expenditure + ") FROM " + table + " WHERE strftime('%m', date)=11";
    Cursor cursor = db.rawQuery(query, null);


    float total = 0;
    if (cursor.moveToFirst())
        total = cursor.getInt(cursor.getColumnIndex("Total"));// get final total

    return total;
}

the table

public static class TableExpenditure {

    public static final String TABLE_EXPENDITURE = "expenditure_table";

    public static final String ATTR_PRIMARY_KEY = "pk_expendituretable";
    public static final String ATTR_DATE = "date";
    public static final String ATTR_CATEGORY = "category";
    public static final String ATTR_EXPENDITURE = "expenditure";
    public static final String ATTR_COMMENT = "comment";

    public static final int N_COLUMNS = 5;

    public static final String SQL_CREATE_TABLE =
            "CREATE TABLE " + TABLE_EXPENDITURE + "(" + ATTR_PRIMARY_KEY + " INTEGER PRIMARY KEY AUTOINCREMENT," +  ATTR_DATE + " DATE," + ATTR_CATEGORY + " TEXT," +
                    ATTR_EXPENDITURE + " REAL," + ATTR_COMMENT + " TEXT"  + ")";
}

In the debugger the cursor contains one item but when calling getInt(..) the app crashes

Answer

The column created for this result:

SUM(" + expenditure + ")

is not called Total.
Change the sql statement to this:

String query = "SELECT SUM(" + expenditure + ") AS Total FROM " + table + " WHERE strftime('%m', date)=11";

This way you give the name (alias) Total to the column.
Or since there is only 1 column returned, do this:

total = cursor.getInt(0);

Advertisement

Answer