إنشاء متوسط في SQLite

3

في تطبيقي لدي 4 أنشطة ، يرتبط كل نشاط بجدول في قاعدة بيانات SQLite.

  1. الدوريات = الدوري
  2. Table Bowlers = طاولة الرامى
  3. سلسلة = جدول السلسلة
  4. الألعاب = لعبة الطاولة

أحاول معرفة أفضل طريقة للحصول على متوسط نقاط لكل سلسلة. لا يتم إنشاء معرفات اللعبة حتى تتم إضافة لعبة جديدة إلى السلسلة.

في الوقت الذي يدخل فيه الرامى في درجاته ، قد تبدو هوية الهوية شيء من هذا القبيل ؛

  • معرف الدوري = 1
  • معرف بولر = 3
  • معرف السلسلة = 6
  • معرف اللعبة = 9،10،11

ما هو أفضل حل لتوليد المتوسط لمعرف السلسلة 6. كنت أفكر في أنه يجب إنشاء المتوسط في كل مرة يتم فيها إدخال النتيجة وكتابتها في حقل متوسط في جدول السلسلة. أيه أفكار؟

الطريقة التي تسرد ألعاب الرامى:

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

2 الاجابة

0

لا أستطيع حقًا فهم ما تريده ، ولكن إذا كنت ترغب في الحصول على متوسط كل الألوان في الجدول ، فقم بما يلي:

1 أضف جميع معرفات السلسلة. (للإضافة: ضع كل القيم في مصفوفة واستخدمها array_sum() )

2 الاستخدام mysqli_num_rows() واحسب جميع الصفوف من الجدول الخاص بك

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 اقسم مجموع المصفوفة على عدد جميع صفوفك.

:مؤلف
0
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
:مؤلف
فوق
قائمة طعام