Return Count(*) in Android SQLite table

Question

Here is my simple function:

public int countCats(String tableName) {
    int catCount = 0;
    Cursor cursor = database.rawQuery("SELECT COUNT(*) FROM " + MySQLiteHelper.TABLE_CAT, null);
    if (cursor != null) {
        catCount = cursor.getColumnIndex("COUNT");
    }

    return catCount;
}

There are 11 rows int this table. But this function returns -1. How is appropriate way to handle this?

EDIT:

I have updated to this:

public int countCats(String tableName) {
    int catCount = 0;
    Cursor cursor = database.rawQuery("SELECT COUNT("+ MySQLiteHelper.COLUMN_CAT+ ") FROM " + MySQLiteHelper.TABLE_CAT, null);
    if (cursor.moveToFirst()) {
        catCount = cursor.getInt(0);
    }
    cursor.close();


    return catCount;
}

Now I get 0;

Note that if you don't know why it returns -1, you can use getColumnIndexOrThrow(String) to see what happens ("if you expect the column to exist use getColumnIndexOrThrow(String) instead, which will make the error more clear.") — Alexis C.
– Alexis C., Commented Jan 4, 2014 at 21:48

laalto · Accepted Answer · 2014-01-04 21:45:33Z

3

You're only asking for the column index for a column COUNT that does not exist in the cursor. Hence -1 is returned.

To retrieve the count value, move the cursor to the first row and get the first column value:

if (cursor.moveToFirst()) {
    catCount = cursor.getInt(0);
}

(A COUNT(*) query will always have a result row but it's a good habit to check the result of moveToFirst() anyway.)

answered Jan 4, 2014 at 21:45

laalto

153k68 gold badges293 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TheLettuceMaster Over a year ago

I have made a small update; now I get 0. I changed code above.

laalto Over a year ago

Chances are that there are no rows. Elsewhere in the question you state that there are 11 - what evidence do you have to support that claim?

TheLettuceMaster Over a year ago

Ok, so code looks right, but it may be the actual table. I believe there are 11, but I am going to confirm. Thanks.

Anatol · Accepted Answer · 2014-01-04 21:57:34Z

3

You forget to call

cursor.moveToFirst();

edited Jan 4, 2014 at 21:57

answered Jan 4, 2014 at 21:43

Anatol

9611 gold badge7 silver badges14 bronze badges

Comments

rekire · Accepted Answer · 2014-01-04 21:50:57Z

1

You are just looking for the index of the result and not the value itself.

So now you know that the value you like to know is in the first column. Now you have to go to the first row and take that value.

cursor.moveToFirst();
int count=cursor.getInt(1);

edited Jan 4, 2014 at 21:50

answered Jan 4, 2014 at 21:45

rekire

48.1k34 gold badges184 silver badges279 bronze badges

2 Comments

laalto Over a year ago

FYI, column indexes start at 0. (bind arg indexing starts at 1)

rekire Over a year ago

I wrote that from mind mixed that up with the column number given from the question.

Collectives™ on Stack Overflow

Return Count(*) in Android SQLite table

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related