SQLite- Obtaining count of rows in a specific column, then returning it as an int value?

Question

I am attempting to write an query that will be used to return the amount of game sessions a user has played from the SQLite database. (i.e. the count of the SessionID column)

I wish implement this as a method with a Query embedded.

I am basing my attempt so far off this somewhat similar method that returns all the information from the database, ie:

// Getting All scores
    public List<Score> getAllScores() {
        List<Score> scoreList = new ArrayList<Score>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_SCORE;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Score score = new Score();
                score.setSessionID(cursor.getString(0));
                score.setGameTitle(cursor.getString(1));
                score.setName(cursor.getString(2));
                score.setMeditation(Integer.parseInt(cursor.getString(3)));
                score.setMax(Integer.parseInt(cursor.getString(4)));
                score.setAvgAttention(Integer.parseInt(cursor.getString(5)));
                score.setMaxAttention(Integer.parseInt(cursor.getString(6)));
                score.setScore(Integer.parseInt(cursor.getString(7)));
                score.setDate(cursor.getString(8));
                // Adding contact to list
                scoreList.add(score);
            } while (cursor.moveToNext());
        }

        // return contact list
        return scoreList;
    }

This is my attempt so far:

public int getTotalGamesPlayed() {
        List<Score> scoreList = new ArrayList<Score>();
        // Select All Query
        String selectQuery = "SELECT  COUNT FROM " + COL_SESSION;

        //is this needed?
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

    //  int to return = return value of the query?



    }

How can I correct this so it actually works?

For reference, the Database structure:

// Database Version
    private static final int DATABASE_VERSION = 10;

    // Database Name
    private final static String DATABASE_NAME = "MeditationDatabase";

    // Contacts table name
    private static final String TABLE_SCORE = "scores";

    // Contacts Table Columns names
    private static final String COL_SESSION = "sessionid";
    private static final String COL_GAMETITLE = "game";
    private static final String COL_NAME = "name";
    private static final String COL_MED = "avgmeditation";
    private static final String COL_MAX = "maxmeditation";
    private static final String COL_AVGATT = "avgattention";
    private static final String COL_MAXATT = "maxattention";
    private static final String COL_SCORE = "score";
    private static final String COL_DATE = "date";

    /**
     * Constructor
     * 
     * @param context
     */
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    /**
     * Method that creates the database
     */
    @Override
    public void onCreate(SQLiteDatabase db) {

        //VERY IMPORTANT: ALWAYS CHECK THAT THERE ARE SPACES AND COMMAS IN CORRECT PLACE IN CODE BELOW:

        String CREATE_TABLE_SCORE = "CREATE TABLE " + TABLE_SCORE + "(" + COL_SESSION
                + " STRING PRIMARY KEY, " + COL_GAMETITLE + " STRING, "  + COL_NAME + " STRING, " + COL_MED + " INTEGER, "
                 + COL_MAX + " INTEGER, " + COL_AVGATT + " INTEGER, " + COL_MAXATT + " INTEGER, "  + COL_SCORE +  " INTEGER, " + COL_DATE + " STRING " + ")";
        db.execSQL(CREATE_TABLE_SCORE);

    }

Answer 1

You can even actually use the built in function from the cursor. If you would like to get the amount of rows from the table Scores, use the following code:

public int getTotalGamesPlayed() {
    List<Score> scoreList = new ArrayList<Score>();
    // Select All Query
    String selectQuery = "SELECT  * FROM " + COL_SESSION;

    //is this needed?
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);


    return cursor.getCount();



}

Never tested the code, but hope it works!

Answer 2

COUNT is a function that expects a parameter that specifies what to count. To count entire rows, use *, like this:

SELECT COUNT(*) FROM scores

However, there is a helper function that simplifies this:

public int getTotalGamesPlayed() {
    SQLiteDatabase db = this.getWritableDatabase();
    try {
        return (int)DatabaseUtils.queryNumEntries(db, TABLE_SCORE);
    } finally {
        db.close();
    }
}