How to get row count in sqlite using Android?

I am creating task manager. I have tasklist and I want when I click on particular tasklist name if it empty then it goes on Add Task activity but if it has 2 or 3 tasks then it shows me those tasks into it in list form.

I am trying to get count in list. my database query is like:

public Cursor getTaskCount(long tasklist_Id) {

	SQLiteDatabase db = this.getWritableDatabase();
	Cursor cursor= db.rawQuery("SELECT COUNT (*) FROM " + TABLE_TODOTASK + " WHERE " + KEY_TASK_TASKLISTID + "=?",
			 new String[] { String.valueOf(tasklist_Id) });
	if(cursor!=null && cursor.getCount()!=0)
          cursor.moveToNext();
	return cursor;
}    

In My activity:

list_tasklistname.setOnItemClickListener(new OnItemClickListener() {
	@Override
	public void onItemClick(AdapterView<?> arg0,
			android.view.View v, int position, long id) {
				db = new TodoTask_Database(getApplicationContext());
				Cursor c = db.getTaskCount(id);
				System.out.println(c.getCount());
				if(c.getCount()>0) {	
				System.out.println(c);
				Intent taskListID = new Intent(getApplicationContext(), AddTask_List.class);
				task = adapter.getItem(position);
				int taskList_id = task.getTaskListId();
				taskListID.putExtra("TaskList_ID", taskList_id);
				startActivity(taskListID);
			}
			else {
				Intent addTask = new Intent(getApplicationContext(), Add_Task.class);
				startActivity(addTask);
			}
		}
	});
	db.close();
}

but when I am clicking on tasklist name it is returning 1, bot number of tasks into it.

Using DatabaseUtils.queryNumEntries():

public long getProfilesCount() {
	SQLiteDatabase db = this.getReadableDatabase();
	long count = DatabaseUtils.queryNumEntries(db, TABLE_NAME);
	db.close();
	return count;
}

or (more inefficiently)

public int getProfilesCount() {
	String countQuery = "SELECT  * FROM " + TABLE_NAME;
	SQLiteDatabase db = this.getReadableDatabase();
	Cursor cursor = db.rawQuery(countQuery, null);
	int count = cursor.getCount();
	cursor.close();
	return count;
}

In Activity:

int profile_counts = db.getProfilesCount();
	db.close();

Use android.database.DatabaseUtils to get number of count.

public long getTaskCount(long tasklist_Id) {
        return DatabaseUtils.queryNumEntries(readableDatabase, TABLE_NAME);
}

It is easy utility that has multiple wrapper methods to achieve database operations.

c.getCount() returns 1 because the cursor contains a single row (the one with the real COUNT(*)). The count you need is the int value of first row in cursor.

public int getTaskCount(long tasklist_Id) {

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor= db.rawQuery(
        "SELECT COUNT (*) FROM " + TABLE_TODOTASK + " WHERE " + KEY_TASK_TASKLISTID + "=?",
         new String[] { String.valueOf(tasklist_Id) }
    );
    int count = 0;
    if(null != cursor)
    	if(cursor.getCount() > 0){
          cursor.moveToFirst();    
          count = cursor.getInt(0);
        }
    	cursor.close();
    }

    db.close();
    return count;
}   

I know it is been answered long time ago, but i would like to share this also:

This code works very well:

SQLiteDatabase db = this.getReadableDatabase();
long taskCount = DatabaseUtils.queryNumEntries(db, TABLE_TODOTASK);

BUT what if i dont want to count all rows and i have a condition to apply?

DatabaseUtils have another function for this: DatabaseUtils.longForQuery

long taskCount = DatabaseUtils.longForQuery(db, "SELECT COUNT (*) FROM " + TABLE_TODOTASK + " WHERE " + KEY_TASK_TASKLISTID + "=?",
         new String[] { String.valueOf(tasklist_Id) });

The longForQuery documentation says:

> Utility method to run the query on the db and return the value in the first column of the first row.

public static long longForQuery(SQLiteDatabase db, String query, String[] selectionArgs)

It is performance friendly and save you some time and boilerplate code

Hope this will help somebody someday :)

Change your getTaskCount Method to this:

public int getTaskCount(long tasklist_id){
	SQLiteDatabase db = this.getWritableDatabase();
	Cursor cursor= db.rawQuery("SELECT COUNT (*) FROM " + TABLE_TODOTASK + " WHERE " + KEY_TASK_TASKLISTID + "=?", new String[] { String.valueOf(tasklist_id) });
	cursor.moveToFirst();
	int count= cursor.getInt(0);
	cursor.close();
	return count;
}

Then, update the click handler accordingly:

public void onItemClick(AdapterView<?> arg0, android.view.View v, int position, long id) {
	db = new TodoTask_Database(getApplicationContext());
	
	// Get task list id
	int tasklistid = adapter.getItem(position).getTaskListId();
	
	if(db.getTaskCount(tasklistid) > 0) {    
		System.out.println(c);
		Intent taskListID = new Intent(getApplicationContext(), AddTask_List.class);
		taskListID.putExtra("TaskList_ID", tasklistid);
		startActivity(taskListID);
	} else {
		Intent addTask = new Intent(getApplicationContext(), Add_Task.class);
		startActivity(addTask);
	}
}

In order to query a table for the number of rows in that table, you want your query to be as efficient as possible. Reference.

Use something like this:

/**
 * Query the Number of Entries in a Sqlite Table
 * */
public long QueryNumEntries()
{
	SQLiteDatabase db = this.getReadableDatabase();
	return DatabaseUtils.queryNumEntries(db, "table_name");
}

Do you see what the DatabaseUtils.queryNumEntries() does? It's awful! I use this.

public int getRowNumberByArgs(Object... args) {
    String where = compileWhere(args);
    String raw = String.format("SELECT count(*) FROM %s WHERE %s;", TABLE_NAME, where);
    Cursor c = getWriteableDatabase().rawQuery(raw, null);
    try {
        return (c.moveToFirst()) ? c.getInt(0) : 0;
    } finally {
        c.close();
    }
}

Sooo simple to get row count:

cursor = dbObj.rawQuery("select count(*) from TABLE where COLUMN_NAME = '1' ", null);
cursor.moveToFirst();
String count = cursor.getString(cursor.getColumnIndex(cursor.getColumnName(0)));

looking at the sources of DatabaseUtils we can see that queryNumEntries uses a select count(*)... query.

public static long queryNumEntries(SQLiteDatabase db, String table, String selection,
        String[] selectionArgs) {
    String s = (!TextUtils.isEmpty(selection)) ? " where " + selection : "";
    return longForQuery(db, "select count(*) from " + table + s,
                selectionArgs);
}

Once you get the cursor you can do

Cursor.getCount()