Generate an Average in SQLite

Question

In my app I have 4 activities, each activity is associated to a table in my SQLite database.

Leagues = League
Table Bowlers = Bowler Table
Series = Series Table
Games = Game Table

I am trying to figure out what would be the best way to generate an average score for each Series. The Game id's are not generated until a new game is added to the series.

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

League id = 1
Bowler id = 3
Series id = 6
Game id's = 9,10,11

What would be the best solution for generating the average for Series id 6. I was thinking that the average should be generated each time a score is entered and written to an average field in the Series table. Any ideas?

Method that lists games for a bowler:

public List<Game> getAllGames(String leagueId, String bowlerId, String seriesId) {
        List<Game> games = new ArrayList<>();

        //Select All Query
        String selectQuery = "SELECT  * 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";

        try (SQLiteDatabase db = this.getWritableDatabase()) {
            @SuppressLint("Recycle") Cursor cursor = db.rawQuery( selectQuery, null );

            //Looping Through All Rows And Adding To The List
            if (cursor.moveToFirst()) {
                do {
                    Game game1 = new Game();
                    game1.setId(cursor.getInt(cursor.getColumnIndex(Game.COLUMN_ID ) ) );
                    game1.setLeagueId(cursor.getString(cursor.getColumnIndex(Game.COLUMN_LEAGUE_ID)));
                    game1.setBowlerId(cursor.getString(cursor.getColumnIndex(Game.COLUMN_BOWLER_ID)));
                    game1.setSeriesId(cursor.getString(cursor.getColumnIndex(Game.COLUMN_SERIES_ID)));
                    game1.setScore(cursor.getString(cursor.getColumnIndex(Game.COLUMN_SCORE)));
                    game1.setStrikes(cursor.getString(cursor.getColumnIndex(Game.COLUMN_STRIKES)));
                    game1.setSpares(cursor.getString(cursor.getColumnIndex(Game.COLUMN_SPARES)));
                    game1.setSplits(cursor.getString(cursor.getColumnIndex(Game.COLUMN_SPLITS)));
                    game1.setSplitConversions(cursor.getString(cursor.getColumnIndex(Game.COLUMN_SPLIT_CONVERSIONS)));
                    game1.setOpenFrames(cursor.getString(cursor.getColumnIndex(Game.COLUMN_OPEN_FRAMES)));
                    game1.setTimestamp(cursor.getString(cursor.getColumnIndex(Game.COLUMN_TIMESTAMP)));
                    games.add( game1 );
                } while (cursor.moveToNext());
            }

            //Close Database Connection
            db.close();
        }

        //Return Game List
        return games;
    }

I was thinking that since I am list all the games for a bowler using the above code, I could just add up the scores as I load them into the listview. How would I go about accomplishing something like this?

DatabaseHelper

public class DatabaseHelper extends SQLiteOpenHelper {

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

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


    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    //Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL(League.CREATE_TABLE);
        db.execSQL(Bowler.CREATE_TABLE);
        db.execSQL(Series.CREATE_TABLE);
        db.execSQL(Game.CREATE_TABLE);

    }



    //Upgrade Database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //Drop Older Table If It Existed
        db.execSQL("DROP TABLE IF EXISTS " + League.TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + Bowler.TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + Series.TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + Game.TABLE_NAME);


        //Create Tables Again
        onCreate( db );
    }

    //************************                ************************
    //************************ League Methods ************************
    //************************                ************************

    public long insertLeague(String leagueName) {
        //Get Writable Database That We Want To Write Data Too
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();

        //`id` and `timestamp` Will Be Inserted Aromatically
        values.put(League.COLUMN_NAME, leagueName);

        //Insert Row
        long id = db.insert(League.TABLE_NAME, null, values);

        //Close Database Connection
        db.close();

        //Return Newly Inserted Row Id
        return id;
    }

    public League getLeague(long id) {
        //Get Readable Database If We Are Not Inserting Anything
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(League.TABLE_NAME,
                new String[]{League.COLUMN_ID, League.COLUMN_NAME, League.COLUMN_TIMESTAMP},
                League.COLUMN_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);

        if (cursor.moveToFirst()) {

            //Prepare League Object
            League league = new League(
                    cursor.getInt(cursor.getColumnIndex(League.COLUMN_ID)),
                    cursor.getString(cursor.getColumnIndex(League.COLUMN_NAME)),
                    cursor.getString(cursor.getColumnIndex(League.COLUMN_TIMESTAMP)));

            //Close Database Connection
            cursor.close();
            return league;
        } else {
            return null;
        }
    }

    public List<League> getAllLeagues() {
        List<League> leagues = new ArrayList<>();

        //Select All Query
        String selectQuery = "SELECT  * FROM " + League.TABLE_NAME + " ORDER BY " +
                League.COLUMN_TIMESTAMP + " DESC";

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

        //Looping Through All Rows And Adding To The List
        if (cursor.moveToFirst()) {
            do {
                League league = new League();
                league.setId(cursor.getInt(cursor.getColumnIndex(League.COLUMN_ID)));
                league.setName(cursor.getString(cursor.getColumnIndex(League.COLUMN_NAME)));
                league.setTimestamp(cursor.getString(cursor.getColumnIndex(League.COLUMN_TIMESTAMP)));

                leagues.add(league);
            } while (cursor.moveToNext());
        }
        cursor.close();
        //Close Database Connection
        db.close();

        //Return League List
        return leagues;
    }

    public int getLeaguesCount() {
        String countQuery = "SELECT  * FROM " + League.TABLE_NAME;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);

        int count = cursor.getCount();
        cursor.close();

        //Return The Count
        return count;
    }

    public int updateLeague(League league) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put( League.COLUMN_NAME, league.getName() );

        //Updating Row
        return db.update(League.TABLE_NAME, values, League.COLUMN_ID + " = ?",
                new String[]{String.valueOf(league.getId())});
    }

    public void deleteLeague(League league) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(League.TABLE_NAME, League.COLUMN_ID + " = ?",
                new String[]{String.valueOf( league.getId())});
        db.close();
    }

    //************************                ************************
    //************************ Bowler Methods ************************
    //************************                ************************

    public long insertBowler(String leagueId, String bowlerName) {
        //Get Writable Database That We Want To Write Data Too
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();

        //`id` and `timestamp` Will Be Inserted Automatically
        values.put(Bowler.COLUMN_LEAGUE_ID, leagueId);
        values.put(Bowler.COLUMN_NAME, bowlerName);

        //Insert Row
        //long id = db.insert(Bowler.TABLE_NAME, null, values);
        long id = db.insertOrThrow( Bowler.TABLE_NAME, null, values );
        Log.d("INSERTBOWLER","Number of bowlers in db = " + String.valueOf( DatabaseUtils.queryNumEntries(db,Bowler.TABLE_NAME)));
        //Close Database Connection
        db.close();

        //Return Newly Inserted Row Id
        return id;
    }

    public Bowler getBowler(String leagueId) {
        //Get Readable Database If We Are Not Inserting Anything
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query( Bowler.TABLE_NAME,
                new String[]{Bowler.COLUMN_ID, Bowler.COLUMN_LEAGUE_ID, Bowler.COLUMN_NAME, Bowler.COLUMN_TIMESTAMP},
                Bowler.COLUMN_LEAGUE_ID + "=?",
                new String[]{String.valueOf(leagueId)}, null, null, null, null);

        Bowler bowler = null;
        if (cursor.moveToFirst()) {

            //Prepare Bowler Object
            bowler = new Bowler(
                    cursor.getInt(cursor.getColumnIndex(Bowler.COLUMN_ID)),
                    cursor.getString(cursor.getColumnIndex(Bowler.COLUMN_LEAGUE_ID)),
                    cursor.getString(cursor.getColumnIndex(Bowler.COLUMN_NAME)),
                    cursor.getString(cursor.getColumnIndex(Bowler.COLUMN_TIMESTAMP)));

            //Close Database Connection
            cursor.close();
            return bowler;
        } else {
            return bowler;
        }
    }
    public List<Bowler> getAllBowlers(String leagueId) {
        List<Bowler> bowlers = new ArrayList<>();

        //Select All Query
        String selectQuery = "SELECT  * FROM " + Bowler.TABLE_NAME + " WHERE " + Bowler.COLUMN_LEAGUE_ID + " = '" + leagueId + "'" + " ORDER BY " +
                Bowler.COLUMN_TIMESTAMP + " DESC";

        Log.d("GETALLBOWLERS-SQL","SQL used = >>>>" +selectQuery + "<<<<");

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

        Log.d("GETALLBOWLERS-CNT","Number of rows retrieved = " + String.valueOf(cursor.getCount()));


        //Looping Through All Rows And Adding To The List
        if (cursor.moveToFirst()) {
            do {
                Bowler bowler = new Bowler();
                bowler.setId(cursor.getInt(cursor.getColumnIndex(Bowler.COLUMN_ID)));
                bowler.setLeagueId(cursor.getString(cursor.getColumnIndex(Bowler.COLUMN_LEAGUE_ID)));
                bowler.setName(cursor.getString(cursor.getColumnIndex(Bowler.COLUMN_NAME)));
                bowler.setTimestamp(cursor.getString(cursor.getColumnIndex(Bowler.COLUMN_TIMESTAMP)));
                bowlers.add(bowler);
            } while (cursor.moveToNext());
        }
        cursor.close();
        //Close Database Connection
        db.close();
        Log.d("GETALLBOWLERS-CNT","Number of elements in bowlerslist = " + String.valueOf(bowlers.size()));

        //Return Bowlers List
        return bowlers;
    }

    public int getBowlersCount() {
        String countQuery = "SELECT  * FROM " + Bowler.TABLE_NAME;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);

        int count = cursor.getCount();
        cursor.close();

        //Return The Count
        return count;
    }

    public int updateBowler(Bowler bowler) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(Bowler.COLUMN_LEAGUE_ID, bowler.getLeagueId());
        values.put(Bowler.COLUMN_NAME, bowler.getName());

        //Updating Row
        return db.update(Bowler.TABLE_NAME, values, Bowler.COLUMN_ID + " = ?",
                new String[]{String.valueOf(bowler.getId())});
    }

    public void deleteBowler(Bowler bowler) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete( Bowler.TABLE_NAME, Bowler.COLUMN_ID + " = ?",
                new String[]{String.valueOf( bowler.getId())});
        db.close();
    }

    //************************                ************************
    //************************ Series Methods ************************
    //************************                ************************

    public long insertSeries(String leagueId, String bowlerId, String seriesName) {

        //Get Writable Database That We Want To Write Data Too
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        //`id` and `timestamp` Will Be Inserted Automatically
        values.put(Series.COLUMN_LEAGUE_ID, leagueId);
        values.put(Series.COLUMN_BOWLER_ID, bowlerId);
        values.put(Series.COLUMN_NAME, seriesName);


        //Insert Row
        //long id = db.insert( Series.TABLE_NAME, null, values );
        long id = db.insertOrThrow( Series.TABLE_NAME, null, values );
        Log.d("INSERT SERIES","Number of series in db = " + String.valueOf( DatabaseUtils.queryNumEntries(db,Series.TABLE_NAME)));

        //Close Database Connection
        db.close();

        //Return Newly Inserted Row Id
        return id;
    }

    public Series getSeries(String leagueId, String bowlerId) {
        //Get Readable Database If We Are Not Inserting Anything
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query( Series.TABLE_NAME,
                new String[]{Series.COLUMN_ID, Series.COLUMN_LEAGUE_ID, Series.COLUMN_BOWLER_ID,
                        Series.COLUMN_NAME, Series.COLUMN_SERIES_AVERAGE, Series.COLUMN_TIMESTAMP},
                Series.COLUMN_LEAGUE_ID + "=?" + " AND " +  Series.COLUMN_BOWLER_ID + "=?",
                new String[]{String.valueOf(leagueId), String.valueOf(bowlerId)}, null, null, null, null);

        if (cursor.moveToFirst()) {

            //Prepare Series Object
            Series series = new Series(
                    cursor.getInt(cursor.getColumnIndex(Series.COLUMN_ID)),
                    cursor.getString(cursor.getColumnIndex(Series.COLUMN_LEAGUE_ID)),
                    cursor.getString(cursor.getColumnIndex(Series.COLUMN_BOWLER_ID)),
                    cursor.getString(cursor.getColumnIndex(Series.COLUMN_NAME)),
                    cursor.getString(cursor.getColumnIndex(Series.COLUMN_SERIES_AVERAGE)),
                    cursor.getString(cursor.getColumnIndex(Series.COLUMN_TIMESTAMP)));

            //Close Database Connection
            cursor.close();
            return series;
        } else {
            return null;
        }
    }

    public List<Series> getAllSeries(String leagueId, String bowlerId) {
        List<Series> series = new ArrayList<>();

        //Select All Query
        String selectQuery = "SELECT * FROM " + Series.TABLE_NAME + " WHERE " + Series.COLUMN_LEAGUE_ID + " = '" + leagueId + "'" + " AND " + Series.COLUMN_BOWLER_ID + " = '" + bowlerId + "'"+ " ORDER BY " +
                Series.COLUMN_TIMESTAMP + " DESC";

        Log.d("GET ALL SERIES-SQL","SQL used = >>>>" +selectQuery + "<<<<");

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

        Log.d("GET ALL SERIESS-CNT","Number of rows retrieved = " + String.valueOf(cursor.getCount()));

        //Looping Through All Rows And Adding To The List
        if (cursor.moveToFirst()) {
            do {
                Series series1 = new Series();
                series1.setId(cursor.getInt(cursor.getColumnIndex(Series.COLUMN_ID)));
                series1.setLeagueId(cursor.getString(cursor.getColumnIndex(Series.COLUMN_LEAGUE_ID)));
                series1.setBowlerId(cursor.getString(cursor.getColumnIndex(Series.COLUMN_BOWLER_ID)));
                series1.setName(cursor.getString(cursor.getColumnIndex(Series.COLUMN_NAME)));
                series1.setAverage(cursor.getString(cursor.getColumnIndex(Series.COLUMN_SERIES_AVERAGE)));
                series1.setTimestamp(cursor.getString(cursor.getColumnIndex(Series.COLUMN_TIMESTAMP)));
                series.add(series1);
            } while (cursor.moveToNext());
        }
        cursor.close();
        //Close Database Connection
        db.close();
        Log.d("GETALLSERIES-CNT","Number of elements in serieslist = " + String.valueOf(series.size()));
        //Return Series List
        return series;
    }

    public int getSeriesCount() {
        String countQuery = "SELECT  * FROM " + Series.TABLE_NAME;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery( countQuery, null );

        int count = cursor.getCount();
        cursor.close();

        //Return The Count
        return count;
    }

    public int updateSeries(Series series) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(Series.COLUMN_LEAGUE_ID, series.getLeagueId());
        values.put(Series.COLUMN_BOWLER_ID, series.getBowlerId());
        values.put(Series.COLUMN_NAME, series.getName());
        values.put(Series.COLUMN_SERIES_AVERAGE,series.getAverage());

        //Updating Row
        return db.update( Series.TABLE_NAME, values, Series.COLUMN_ID + " = ?",
                new String[]{String.valueOf( series.getId() )} );

    }

    public void deleteSeries(Series series) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete( Series.TABLE_NAME, Series.COLUMN_ID + " = ?",
                new String[]{String.valueOf( series.getId() )} );
        db.close();
    }

    //************************                ************************
    //************************  Game Methods  ************************
    //************************                ************************

    public long insertGame(String leagueId, String bowlerId, String seriesId, String score, String strikes, String spares, String splits, String splitConversions, String openFrames) {

        //Get Writable Database That We Want To Write Data Too
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        //`id` and `timestamp` Will Be Inserted Automatically
        values.put(Game.COLUMN_LEAGUE_ID, leagueId);
        values.put(Game.COLUMN_BOWLER_ID, bowlerId);
        values.put(Game.COLUMN_SERIES_ID, seriesId);
        values.put(Game.COLUMN_SCORE, score);
        values.put(Game.COLUMN_STRIKES, strikes);
        values.put(Game.COLUMN_SPARES, spares);
        values.put(Game.COLUMN_SPLITS, splits);
        values.put(Game.COLUMN_SPLIT_CONVERSIONS, splitConversions);
        values.put(Game.COLUMN_OPEN_FRAMES, openFrames);

        //Insert Row
        long id = db.insert( Game.TABLE_NAME, null, values );

        //Close Database Connection
        db.close();

        //Return Newly Inserted Row Id
        return id;
    }

    public Game getGame(String leagueId, String bowlerId, String seriesId) {
        //Get Readable Database If We Are Not Inserting Anything
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query( Game.TABLE_NAME,
                new String[]{Game.COLUMN_ID, Game.COLUMN_LEAGUE_ID, Game.COLUMN_BOWLER_ID,
                        Game.COLUMN_SERIES_ID, Game.COLUMN_SCORE, Game.COLUMN_STRIKES, Game.COLUMN_SPARES, Game.COLUMN_SPLITS, Game.COLUMN_SPLIT_CONVERSIONS, Game.COLUMN_OPEN_FRAMES, Game.COLUMN_TIMESTAMP},
                Game.COLUMN_LEAGUE_ID + "=?" + " AND " + Game.COLUMN_BOWLER_ID + "=?" + " AND " + Game.COLUMN_SERIES_ID + "=?",
                new String[]{String.valueOf( leagueId ), String.valueOf( bowlerId ), String.valueOf( seriesId )}, null, null, null, null );

        if (cursor.moveToFirst()) {

            //Prepare Game Object
            Game game = new Game(
                    cursor.getInt( cursor.getColumnIndex(Game.COLUMN_ID)),
                    cursor.getString( cursor.getColumnIndex(Game.COLUMN_LEAGUE_ID)),
                    cursor.getString( cursor.getColumnIndex(Game.COLUMN_BOWLER_ID)),
                    cursor.getString( cursor.getColumnIndex(Game.COLUMN_SERIES_ID)),
                    cursor.getString( cursor.getColumnIndex(Game.COLUMN_SCORE)),
                    cursor.getString( cursor.getColumnIndex(Game.COLUMN_STRIKES)),
                    cursor.getString( cursor.getColumnIndex(Game.COLUMN_SPARES)),
                    cursor.getString( cursor.getColumnIndex(Game.COLUMN_SPLITS)),
                    cursor.getString( cursor.getColumnIndex(Game.COLUMN_SPLIT_CONVERSIONS)),
                    cursor.getString( cursor.getColumnIndex(Game.COLUMN_OPEN_FRAMES)),
                    cursor.getString( cursor.getColumnIndex(Game.COLUMN_TIMESTAMP)));

            //Close Database Connection
            cursor.close();
            return game;
        } else {
            return null;
        }
    }

    public List<Game> getAllGames(String leagueId, String bowlerId, String seriesId) {
        List<Game> games = new ArrayList<>();

        //Select All Query
        String selectQuery = "SELECT  * 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";

        try (SQLiteDatabase db = this.getWritableDatabase()) {
            @SuppressLint("Recycle") Cursor cursor = db.rawQuery( selectQuery, null );

            //Looping Through All Rows And Adding To The List
            if (cursor.moveToFirst()) {
                do {
                    Game game1 = new Game();
                    game1.setId(cursor.getInt(cursor.getColumnIndex(Game.COLUMN_ID ) ) );
                    game1.setLeagueId(cursor.getString(cursor.getColumnIndex(Game.COLUMN_LEAGUE_ID)));
                    game1.setBowlerId(cursor.getString(cursor.getColumnIndex(Game.COLUMN_BOWLER_ID)));
                    game1.setSeriesId(cursor.getString(cursor.getColumnIndex(Game.COLUMN_SERIES_ID)));
                    game1.setScore(cursor.getString(cursor.getColumnIndex(Game.COLUMN_SCORE)));
                    game1.setStrikes(cursor.getString(cursor.getColumnIndex(Game.COLUMN_STRIKES)));
                    game1.setSpares(cursor.getString(cursor.getColumnIndex(Game.COLUMN_SPARES)));
                    game1.setSplits(cursor.getString(cursor.getColumnIndex(Game.COLUMN_SPLITS)));
                    game1.setSplitConversions(cursor.getString(cursor.getColumnIndex(Game.COLUMN_SPLIT_CONVERSIONS)));
                    game1.setOpenFrames(cursor.getString(cursor.getColumnIndex(Game.COLUMN_OPEN_FRAMES)));
                    game1.setTimestamp(cursor.getString(cursor.getColumnIndex(Game.COLUMN_TIMESTAMP)));
                    games.add( game1 );
                } while (cursor.moveToNext());
            }

            //Close Database Connection
            db.close();
        }

        //Return Game List
        return games;
    }

    public int getGameCount() {
        String countQuery = "SELECT  * FROM " + Game.TABLE_NAME;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery( countQuery, null );

        int count = cursor.getCount();
        cursor.close();

        //Return The Count
        return count;
    }

    public int updateGame(Game game) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(Game.COLUMN_LEAGUE_ID, game.getLeagueId());
        values.put(Game.COLUMN_BOWLER_ID, game.getBowlerId());
        values.put(Game.COLUMN_SERIES_ID, game.getSeriesId());
        values.put(Game.COLUMN_SCORE, game.getScore());
        values.put(Game.COLUMN_STRIKES, game.getStrikes());
        values.put(Game.COLUMN_SPARES, game.getSpares());
        values.put(Game.COLUMN_SPLITS, game.getSplits());
        values.put(Game.COLUMN_SPLIT_CONVERSIONS, game.getSplitConversions());
        values.put(Game.COLUMN_OPEN_FRAMES, game.getOpenFrames());

        //Updating Row
        return db.update( Game.TABLE_NAME, values, Game.COLUMN_ID + " = ?",
                new String[]{String.valueOf( game.getId() )} );

    }

    public void deleteGame(Game game) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(Game.TABLE_NAME, Game.COLUMN_ID + " = ?",
                new String[]{String.valueOf( game.getId() )} );
        db.close();
    }
}

Frank · Accepted Answer · 2018-07-20 14:21:26Z

1

sqlite> create table test (a int);
sqlite> insert into test values (1);
sqlite> insert into test values (2);
sqlite> insert into test values (3);
sqlite> insert into test values (4);
sqlite> select avg(a) as average, count(a) as count, sum(a) as sum from test;
average     count       sum       
----------  ----------  ----------
2.5         4           10        
sqlite> select avg(a) as average, count(a) as count, sum(a) as sum from test
  where a > 2;
average     count       sum       
----------  ----------  ----------
3.5         2           7

answered Jul 20, 2018 at 14:21

Frank

3381 gold badge2 silver badges10 bronze badges

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

8 Comments

Frank Over a year ago

Yes- there are a number of core math functions.

Robert Vogl Over a year ago

I tried to follow the link above but I am getting the following "Document Not Found The document /lang_corefunc.html_ is not available on this server"

Robert Vogl Over a year ago

How can I assign the results of avg(col) to a variable.

Frank Over a year ago

Remove the underscore from the end of the URL. You would assign the results of a query to a variable the same way you're doing it in your posting- it's just a column value like anything else.

Robert Vogl Over a year ago

If the results for avg(col) is just another column in the table, is it safe to assume that the column name is avg or average?

|

Jakob · Accepted Answer · 2018-07-20 15:00:36Z

0

I can't really understand what you want, but if you want to get the average of all colums in a table than do the following:

1 Add all Series id's. (For adding: Put all values in an Array and use array_sum())

2 Use mysqli_num_rows() and count all rows from your table

How to get the average:

Add up all the numbers, then divide by how many numbers there are. In other words it is the sum divided by the count.

3 Divide the Array sum by the number of all your rows.

answered Jul 20, 2018 at 15:00

Jakob

1,9352 gold badges17 silver badges32 bronze badges

2 Comments

Robert Vogl Over a year ago

I am trying to get the average score of all the games in the Game table that correspond to a series of id's then write the average score to the Series table to be displayed with the particular Series that matches the games.

Frank Over a year ago

question was specifically about SQLite.

Collectives™ on Stack Overflow

Generate an Average in SQLite

2 Answers 2

8 Comments

How to get the average:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

How to get the average:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related