AVG(COLUMN_SCORE) Not Generating Average

Question

In my App. I have a series of for tables.

• League
• Bowler
• Series
• Game

By the time a bowler enters in his/her scores the id's might look something like this;

• League id = 2
• Bowler id = 1
• Series id = 1
• Game id's = 1,2,3

In my Games table I am recording the scores for each game played (i.e. 1,2,3). I want to be able to generate an Average Score for a series (i.e. 1).

I know that this can be done with

CREATE VIEW AVERAGE_SCORE AS SELECT AVG(COLUMN_SCORE) FROMFROM " + Game.TABLE_NAME + " WHERE " + Game.COLUMN_LEAGUE_ID + " = '" + leagueId + "'" + " AND " + Game.COLUMN_BOWLER_ID + " = '" + bowlerId + "'" + " AND " + Game.COLUMN_SERIES_ID + " = '" + seriesId + "'" + " ORDER BY " +
        Game.COLUMN_TIMESTAMP + " DESC";

I have managed to get a value to appear in logcat using the following code:

public int getSeriesAverage(String leagueId, String bowlerId, String seriesId)
    {
        int total = 0;
        SQLiteDatabase database = getReadableDatabase();
        Cursor cursor = database.rawQuery("CREATE VIEW IF NOT EXISTS AVERAGE_SCORE AS SELECT AVG(COLUMN_SCORE) FROM " + Game.TABLE_NAME + " WHERE " + Game.COLUMN_LEAGUE_ID + " = '" + leagueId + "'" + " AND " + Game.COLUMN_BOWLER_ID + " = '" + bowlerId + "'" + " AND " + Game.COLUMN_SERIES_ID + " = '" + seriesId + "'" + " ORDER BY " +
                Game.COLUMN_TIMESTAMP + " DESC", null);
        if (cursor.moveToFirst()) {
            do {
            total = cursor.getInt(0);
        }while (cursor.moveToNext());
        }
        cursor.close();
        //Close Database Connection
        database.close();
        Log.d("GET AVERAGE FROM SQL","Average = >>>>" + total + "<<<<");
        return total;
    }

However it does not appear to be generating an average value. In my database I have three scores Game 1 = 222, Game 2 = 300, Game 3 = 200. The average should be 240. But Log.d shows it as 0.

07-24 19:58:53.385 7287-7287/ca.rvogl.tpbcui D/GET AVERAGE FROM SQL: Average = >>>>0<<<<

What am I doing wrong, any assistance would be greatly appreciated

Changed Code:

public int getSeriesAverage(String leagueId, String bowlerId, String seriesId)
    {
        int total = 0;
        SQLiteDatabase database = getReadableDatabase();
        Cursor cursor = database.rawQuery("SELECT AVG(COLUMN_SCORE) FROM " + Game.TABLE_NAME + " WHERE " + Game.COLUMN_LEAGUE_ID + " = '" + leagueId + "'" + " AND " + Game.COLUMN_BOWLER_ID + " = '" + bowlerId + "'" + " AND " + Game.COLUMN_SERIES_ID + " = '" + seriesId + "'", null);
        if (cursor.moveToFirst()) {
            do {
            total = cursor.getInt(0);
        }while (cursor.moveToNext());
        }
        cursor.close();
        //Close Database Connection
        database.close();
        Log.d("GET AVERAGE FROM SQL","Average = >>>>" + total + "<<<<");
        return total;
    }

Series Class:

public class Series {

    public static final String TABLE_NAME = "Series";

    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_LEAGUE_ID = "league_id";
    public static final String COLUMN_BOWLER_ID = "bowler_id";
    public static final String COLUMN_NAME = "name";
    public static final String COLUMN_SERIES_AVERAGE = "average";
    public static final String COLUMN_TIMESTAMP = "timestamp";

    private int id;
    private String league_id;
    private String bowler_id;
    private String name;
    private String average;
    private String timestamp;


    // Create table SQL query
    public static final String CREATE_TABLE =
            "CREATE TABLE " + TABLE_NAME + "("
                    + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                    + COLUMN_LEAGUE_ID + " TEXT,"
                    + COLUMN_BOWLER_ID + " TEXT,"
                    + COLUMN_NAME + " TEXT,"
                    + COLUMN_SERIES_AVERAGE + " TEXT,"
                    + COLUMN_TIMESTAMP + " DATETIME DEFAULT CURRENT_TIMESTAMP"
                    + ")";

    public Series() {
    }

    public Series(int id, String league_id, String bowler_id, String name, String average, String timestamp) {
        this.id = id;
        this.league_id = league_id;
        this.bowler_id = bowler_id;
        this.name = name;
        this.average = average;
        this.timestamp = timestamp;
    }

    public int getId() {
        return id;
    }

    public String getLeagueId() {return league_id;}

    public String getBowlerId() {return bowler_id;}

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAverage() {
        return average;
    }

    public void setAverage(String average) {
        this.average = average;
    }

    public String getTimestamp() {
        return timestamp;
    }

    public void setId(int id) {
        this.id = id;
    }

    public void setLeagueId(String league_id) {
        this.league_id = league_id;
    }

    public void setBowlerId(String bowler_id) {
        this.bowler_id = bowler_id;
    }

    public void setTimestamp(String timestamp) {
        this.timestamp = timestamp;
    }
}

Updated Method:

 public int getSeriesAverage(String leagueId, String bowlerId, String seriesId)
    {
        int total = 0;
        SQLiteDatabase database = getReadableDatabase();
        Cursor cursor = database.rawQuery("SELECT AVG(" + Game.COLUMN_SCORE + ") FROM " + Game.TABLE_NAME + " WHERE " + Game.COLUMN_LEAGUE_ID + " = '" + leagueId + "'" + " AND " + Game.COLUMN_BOWLER_ID + " = '" + bowlerId + "'" + " AND " + Game.COLUMN_SERIES_ID + " = '" + seriesId + "'", null);
        if (cursor.moveToFirst()) {
            do {
            total = cursor.getInt(0);
        }while (cursor.moveToNext());
        }
        cursor.close();
        //Close Database Connection
        database.close();
        Log.d("GET AVERAGE FROM SQL","Average = >>>>" + total + "<<<<");
        ContentValues values = new ContentValues();
        values.put(Series.COLUMN_SERIES_AVERAGE,Series.getAverage());
        Log.d("GET AVERAGE FROM SQL","Average = >>>>" + Series.COLUMN_SERIES_AVERAGE + "<<<<");
        return total;
    }

Answer 1

Your sql statement is a CREATE statement and not a query to fetch rows. So change it:

"SELECT AVG(" + Game.COLUMN_SCORE + ") FROM " + Game.TABLE_NAME + " WHERE " + Game.COLUMN_LEAGUE_ID + " = '" + leagueId + "'" + " AND " + Game.COLUMN_BOWLER_ID + " = '" + bowlerId + "'" + " AND " + Game.COLUMN_SERIES_ID + " = '" + seriesId + "'"

You might also need to change: total = cursor.getInt(0);
I'm not sure if the average value which will be produced will be rounded to int.
Better use getDouble or getString and then parse to int