How to get the row count of a query in Android using SQLite?

Question

How do I get the row count of a query in Android using SQLite? It seems my following method does not work.

public int getFragmentCountByMixId(int mixId) {
    int count = 0;
    SQLiteDatabase db = dbOpenHelper.getWritableDatabase();

    Cursor cursor = db.rawQuery(
        "select count(*) from downloadedFragement where mixId=?",
        new String[]{String.valueOf(mixId)});
    while(cursor.moveToFirst()){
        count = cursor.getInt(0);
    }
    return count;
}

Unrelated to question: You can't iterate a cursor by calling "moveToFirst" indefinitely. You should call it once and then use while(cursor.moveToNext()) { ... — Ankhwatcher, Oct 26 '17 at 16:02

score 107 · Accepted Answer · edited Apr 15 '12 at 14:52

107

Cursor.getCount()

edited Apr 15 '12 at 14:52

answered Jun 15 '11 at 00:07

michaelg

2,692
1
17
16

4

I don't know how this would work with your current implementation but if you simply change count(*) to *, this should work. – Noel Jun 15 '11 at 00:18
3

I think best solution `return (int) DatabaseUtils.longForQuery(mDB, "SELECT COUNT(*) FROM table_name", null);` – Pratik Butani Sep 11 '15 at 07:42
This would return 0 for a cursor that has 1 row. – IgorGanapolsky Nov 20 '15 at 17:01

score 8 · Answer 2 · answered Dec 19 '12 at 10:40

8

cursor.moveToNext();
cursor.getCount();

If the moveToNext() is not called, the cursorIndexOutOfBoundException may arise.

answered Dec 19 '12 at 10:40

Pramod Setlur

811
1
15
27

score 6 · Answer 3 · answered Sep 11 '15 at 07:47

6

This would be more efficient because work for all versions:

int numRows = DatabaseUtils.longForQuery(db, "SELECT COUNT(*) FROM table_name", null);

or

int numRows = DatabaseUtils.queryNumEntries(db, "table_name");

or if you want to get number of rows which specific selection then you should go with (added in API 11)

public static long queryNumEntries (SQLiteDatabase db, String table, String selection)

Thanks :)

answered Sep 11 '15 at 07:47

Pratik Butani

60,504
58
273
437

In you first example you use longForQuery(). I thought that method only returns the value in the first column of the first row. So how would that show the total number of rows in the database? Is it because the query SELECT COUNT(*) is run on the full database? – AJW Sep 13 '18 at 23:35

score 1 · Answer 4 · answered Jul 26 '12 at 18:17

use String instead of int

String strCount = "";
int count = 0;
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();

Cursor cursor = db.rawQuery(
    "select count(*) from downloadedFragement where mixId=?",
    new String[]{String.valueOf(mixId)});

while(cursor.moveToFirst()){
    strCount = cursor.getString(cursor.getColumnIndex("COUNT(*)"));
}
count = Integer.valueOf(strCount).intValue();

score 0 · Answer 5 · answered Oct 16 '15 at 19:14

0

In DatabaseUtils

public static long queryNumEntries(SQLiteDatabase db, String table)

answered Oct 16 '15 at 19:14

James Wierzba

16,176
14
79
120

What if it's an in-memory database, with no database name? – IgorGanapolsky Nov 20 '15 at 17:02
Is "DatabaseUtils.queryNumEntries" still a good choice or has it been deprecated? – AJW Jan 09 '16 at 16:55

score 0 · Answer 6 · answered Nov 28 '18 at 20:24

 public long getRecords() {
    return DatabaseUtils.longForQuery(db, "SELECT COUNT(*) FROM contacts", null);
}

You can use this as a method or use this if your database uses auto increment

 public long getRecords() {
    return DatabaseUtils.longForQuery(db, "SELECT seq FROM sqlite_sequence", null);
}

score 0 · Answer 7 · edited Jan 08 '19 at 20:05

0

val query = "SELECT * FROM $TABLE_NAME ;"
val result = db.rawQuery(query,null)
Toast.makeText(ctx,result.count,Toast.LENGTH_SHORT).show()

edited Jan 08 '19 at 20:05

shim

9,289
12
69
108

answered Jan 08 '19 at 19:33

Stan

1

score 0 · Answer 8 · answered Jun 15 '11 at 00:18

0

Query for _ID column in table and then call getCount on cursor. Here is a link I am doing in one of my project. Look at line number 110.

answered Jun 15 '11 at 00:18

Gopal

1,719
12
13

How to get the row count of a query in Android using SQLite?

8 Answers8

Linked