How to get all data from a single SQLite database column for calculating total price

Question

I'm currently making a shopping app with a shopping cart. I got the listview to display with the proper quantity and price but now have problems with trying to show the total price for checkout. There are 2 problems I'm facing :

1. How to get data from a whole column, right now I am only able to get data from the first index of the intended column.

2. When I clear the database/clear the cart, the app crashes, saying there is a null. Probably due to the cursor not able to get the read anything from the database.

CartDatabaseHelper.java getCartPrice() method

public Double getCartPrice() {
    String query = "SELECT * FROM " + CartContract.CartEntry.TABLE_NAME + " WHERE Cart_Meal_Price";
    SQLiteDatabase dbCart;
    dbCart = this.getWritableDatabase();
    Cursor cursor = dbCart.rawQuery(query, null);
    cursor.moveToFirst();
    do{if (cursor!=null) {
        Double totalPrice = cursor.getDouble(2);
        Double sum =+ totalPrice;
        return sum;
    } else {
        return 0.00;
    }}while(cursor.moveToNext());
}

CartDataBaseHelper.java Declaration and Initialization of cart database

public static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = "CartDatabase";
public static final String CART_TABLE = "CartTable";

public static final String CREATE_CART_TABLE = "CREATE TABLE " + CartContract.CartEntry.TABLE_NAME + " ( " +
        CartContract.CartEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
        CartContract.CartEntry.COLUMN_NAME + " TEXT ," +
        CartContract.CartEntry.COLUMN_PRICE + " REAL , " +
        CartContract.CartEntry.COLUMN_QUANTITY + " REAL ) " ;

SummaryActivity.java getTotalPrice() method

public void getTotalPrice() {
    cartDB = new CartDatabaseHelper(this);
    double total = cartDB.getCartPrice();
    totalprice.setText("Total Price : RM" + total);
    }

SummaryActivity.java Clear database onClickListener

clearthedata.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {
            int deletethedata = getContentResolver().delete(CartContract.CartEntry.CONTENT_URI, null, null);
            cartDB = new CartDatabaseHelper(context);
            double total = cartDB.getCartPrice();
            if (cartDB != null) {
                totalprice.setText("Total Price : RM" + total);
            } else {
                totalprice.setText("Total Price : RM0.00");
            }
        }
    });

Answer 1

If you want to get the sum of all the values of the column CartContract.CartEntry.COLUMN_PRICE in the table, then you need a query like:

SELECT COALESCE(SUM(price), 0) FROM tablename

Here COALESCE() is used to return 0 just in case the table is empty.

So your code should be:

public double getCartPrice() {
    String query = "SELECT COALESCE(SUM(" + CartContract.CartEntry.COLUMN_PRICE + "), 0) FROM " + CartContract.CartEntry.TABLE_NAME;
    SQLiteDatabase dbCart = this.getWritableDatabase();
    Cursor cursor = dbCart.rawQuery(query, null);
    cursor.moveToFirst();
    double total = cursor.getDouble(0);
    cursor.close();
    dbCart.close();
    return total;
}

If you want to delete all the rows of the table, then:

SQLiteDatabase dbCart = this.getWritableDatabase();
dbCart.delete(CartContract.CartEntry.TABLE_NAME, null, null);