Return sum(item_price) from sqlite in Android

Question

I'm trying to get the sum from the specific column in the database. I think my query is ok but I think it's something wrong with receiving it back. Please direct me to the right way.

Thank you so much in advance.

public static double countPrice (SQLiteDatabase db, int selectedID){
    String[] sumPrice = new String[]{"sum(item_price)"};
    String selection = "list_id =? AND item_flag =?";
    String[] selectionArgs = new String[]{String.valueOf(selectedID), String.valueOf(0)};

    Cursor c = db.query(TABLE_NAME, sumPrice, selection, selectionArgs, null, null, null);

    double result = c.getCount();
    return result;
}

https://stackoverflow.com/a/20582538/4168607 – ADM May 22 '18 at 04:39 — ADM, May 22 '18 at 04:39
Thanks your reply, I will try and get back with the result. – Prin Puyakul May 22 '18 at 05:24 — Prin Puyakul, May 22 '18 at 05:24

score 2 · Accepted Answer · answered May 22 '18 at 05:28

You are using the Cursor getCount() method which will return the number of rows, which would be 1 as the query is returning an aggregate (i.e. the sum).

Instead you need to

a) move to the first row in the cursor and then
b) read/extract the data from the respective column using an appropriate Cursor get??? method.

As such your code could be :-

public static double countPrice (SQLiteDatabase db, int selectedID){
    String[] sumPrice = new String[]{"sum(item_price)"};
    String selection = "list_id =? AND item_flag =?";
    String[] selectionArgs = new String[]{String.valueOf(selectedID), String.valueOf(0)};

    Cursor c = db.query(TABLE_NAME, sumPrice, selection, selectionArgs, null, null, null);

    Double result = 0; // default value to signify nothing extracted
    if(c.moveToFirst()) { // move to the first(only) row in the Cursor
        result = c.getDouble(0); // get the value from the first column
    }
    c.close(); // Should always close cursors when done with them
    return result; // Ok to return extracted value (or default for nothing extracted)
}

Thank you so much for your help, it works and I learn something new today, appreciated. =) — Prin Puyakul, May 22 '18 at 05:32

score 1 · Answer 2 · answered May 22 '18 at 04:38

1

You are returning cursor.getCount() which returns the number of rows but not the sum. Android Cursor

Use the following code :

Cursor cur = db.rawQuery("SELECT SUM(myColumn) FROM myTable", null);
if(cur.moveToFirst())
{

return cur.getInt(0);
}

answered May 22 '18 at 04:38

Mohit Navandar

11
1

Hi, Thanks for your reply, i tried `double result; if(c.moveToFirst()) { result = c.getDouble(0); } return result;` but it give me error said the result is not initialized, any idea why? – Prin Puyakul May 22 '18 at 05:22
actually i know why, my silly mistake =/, But thanks so much for your help =) appreciated. – Prin Puyakul May 22 '18 at 05:31

Return sum(item_price) from sqlite in Android

2 Answers2