Sqlite Query in Android to Count Rows

How to count row of a table from sqlite database android

Try This

private static final String DB_TABLE_PLACES = "Places";
private SQLiteDatabase mDatabase;

private long fetchPlacesCount() {
    String sql = "SELECT COUNT(*) FROM " + DB_TABLE_PLACES;
    SQLiteStatement statement = mDatabase.compileStatement(sql);
    long count = statement.simpleQueryForLong();
    return count;
}

See More Here.

How to get row count in sqlite using Android?

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();

How To Use SQLite COUNT in Android to return number of rows

I think the zero parameter is overriding the results

I have no idea what you think that this means.

When I remove the zero the code breaks

That is because getInt() needs to know the column of the Cursor to retrieve.

You are also crashing at runtime, as your SQL is invalid. Your SQL statement amounts to:

SELECT COUNT(foo) WHERE foo = left

(where foo is whatever TableData.TableInfo.DIRECTION in Java refers to)

Not only does your SQL statement lack a table to query against, but if left is supposed to be the value of a string column, you need to quote it. You will wind up with something like:

SELECT COUNT(foo) FROM tablename WHERE foo = 'left'

do I need to put a while loop around the query to move the cursor for a COUNT query?

No.

Is there another way of counting the rows where the string value is 'left' and the sum can be returned?

Not really, other than the fix that I outline above.

how to get rows count of SQLite in Android

Assign totalRowCount = 0;
Before calling your DB Method...

And Why do you want it on every scroll?
It is count of your DB...and will not change on scroll.
You just need to get it once.

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

Cursor.getCount()

Get row count from SQLite

Try like below:

public int getIds()
    {
    String selectQuery = "SELECT Pst_id FROM student_posts";           
    SQLiteDatabase database = this.dbOpenHelper.getReadableDatabase();
    Cursor c = database.rawQuery(selectQuery, null);
    c.moveToFirst();
    int total = c.getCount();
    c.close();

    return total;
}

How do I get updated row count using sqlite in Android?

In the SQLite C API, after a program has executed an UPDATE statement, the number of changed rows can be determined by calling sqlite3_changes().

In the Android framework, the only place where sqlite3_changes() is called is the update() method.

If you need to do UPDATEs that are too complex for update(), you have to execute a second query to call the built-in changes() function:

db.execSQL("UPDATE ...");
long changes = DatebaseUtils.longForQuery(db, "SELECT changes()", null);

Alternatively, you can enable the deprecated PRAGMA count_changes (hoping that it is available) to return the number from the UPDATE statement itself as if it were a query:

class MyOpenHelper {
    ...
    void onConfigure(SQLiteDatabase db) {
        db.execSQL("PRAGMA count_changes = ON");
    }
}

...

changes = DatebaseUtils.longForQuery(db, "UPDATE ...", null);