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

Question

I want to write a query that add up all the rows that have the string value of "left" in column named DIRECTION. Next I want to return this sum.

In my code snip-it below assume data and data base are established.

Here is the prototype:

public int getSumLeft() {
    String selectQuery =  "SELECT COUNT( "+TableData.TableInfo.DIRECTION+" ) WHERE "+TableData.TableInfo.DIRECTION+" = left";
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);
    cursor.moveToFirst();
    int sum = cursor.getInt(0);
    cursor.close();
    return sum;
}

I've tried several queries and this one seems to be the closes to what I need. I think the problem is with statement 'int sum = cursor.getInt(0);'

I think the zero parameter is overriding the results. When I remove the zero the code breaks. getInt is an SQLite function that is used to access data in the database. I did not create that function. But I must use it or and another function like it.

Also, do I need to put a while loop around the query to move the cursor for a COUNT query? Doesn't the Database count for you, therefor no need for iteration?

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

Full code here:

Database: https://github.com/Leoa/Accelerometer/tree/AccelerometerDEV/app/src/main/java/thedatabase

Implementation (see the button in onCreate function ): https://github.com/Leoa/Accelerometer/blob/AccelerometerDEV/app/src/main/java/com/leobee/accelerometer/MainActivity.java

Thanks for looking into this.

CommonsWare · Accepted Answer · 2014-12-31 23:49:56Z

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.

hugohabel · Accepted Answer · 2014-12-31 23:49:44Z

1

I think the problem is you need to add quotes on the 'left'

String selectQuery =  "SELECT COUNT( "+TableData.TableInfo.DIRECTION+" ) WHERE "+TableData.TableInfo.DIRECTION+" = 'left'"

answered Dec 31, 2014 at 23:49

hugohabel

7246 silver badges17 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related