tunc tunc - 1 month ago 13
Android Question

How to get value if a column is 0?

Example database:

title price
book 55
book 15
pencil 5
rubber 0 (default int value)
pencil 3


Result is: book average 35 , pencilaverage 4

OK but I want to see "rubber average 0", too.

I tried:

if ( cursor.moveToFirst() ) {
.
} else {
list.add(0);
}


if (cursor.getCount() ==0){
list.add(0);
}


if (cursor.isBeforeFirst(){
list.add(0);
}


Maybe I put it in the wrong place.

This is my code.

public List<Integer> avarage() {
List<Integer> list = new ArrayList<Integer>();
String selectQuery = "select title, avg(price) from tuncTAB WHERE (price>'" + 0 + "') group by title";
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);//selectQuery,selectedArguments


if (cursor.moveToFirst()) {
do {
Float fif = Float.parseFloat(cursor.getString(1));
Integer pri = Math.round(fif);
list.add(pri);
}
while (cursor.moveToNext());
}

cursor.close();
db.close();
return list;
}

Answer

In SQLite an empty INTEGER column does not default to 0, but to NULL.

You could set the default value of the price column to 0 using the DEFAULT keyword, when creating the table.

For example:

private static final String CREATE_TABLE_PRODUCTS = "CREATE TABLE "
            + TABLE_PRODUCTS + "("
            + COLUMN_TITLE + " TEXT,"
            + COLUMN_PRICE + " INTEGER DEFAULT 0)";