Search code examples
androidsqlitecountcursor

Query to get number of rows based on condition

In my application,I need to get the total number of rows count that has particular value in "status"column.This is the code I tried,

String unreadquery="(SELECT COUNT(*)FROM TABLE_MESSAGES WHERE KEY_STATUS="0")";
Cursor cursor = db.rawQuery(unreadquery, null);

int a=cursor.getCount();

But it was showing error in query statement.How can I get the number of rows count that has 0 value in particular column?

Thanks!

This is code to create table:

public static final String TABLE_MESSAGES = "Messages";

String CREATE_MESSAGES_TABLE = "CREATE TABLE " + TABLE_MESSAGES + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_FROM + " TEXT," + KEY_TO + " TEXT,"
            + KEY_BODY + " TEXT," + KEY_DATE + " TEXT," + KEY_STATUS + " TEXT" + ")";
            db.execSQL(CREATE_MESSAGES_TABLE);
Solution

  • If KEY_STATUS is a text column, use single quote. Also, from your comments, TABLE_MESSAGES and KEY_STATUS are constants that define table and column names.

    String unreadquery="SELECT COUNT(*) FROM " 
    + TABLE_MESSAGES + " WHERE " + KEY_STATUS + "='0'";