GROUP BY in android

Question

what is problem in this code when i use the GROUP BY in android. Please test this code in sqlite :

public void getPlayerName_OrderBy(int First_TeamID)
 {
       int Match_ID = get_MatchID();         

       int matchId;
       int teamID;
       int PlayerID;
       String Name;
        if(Player_SD != null)
            Player_SD.clear();

               String selectQuery = "SELECT "+ STStrikerPlayerName+", count +"("+STStrikerPlayerName+")" +" FROM " +TABLE_SCORE+" where "+ STMatchID +" = '"+ Match_ID +"'"+" AND "      +STFristTeamID + "= '"+First_TeamID+"'"+" GROUP BY "+STStrikerPlayerName;

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

       if (cursor.moveToFirst()) {
            do {
                Player_score_details psd = new Player_score_details();

                matchId = Integer.parseInt(cursor.getString(0));    
                teamID = Integer.parseInt(cursor.getString(1));                                     


                PlayerID = Integer.parseInt(cursor.getString(3));                   


                Name = cursor.getString(20); 



            } while (cursor.moveToNext());
        }

        db.close();    


 }

Answer 1

This is the main Solution ...

public void getPlayerName_OrderBy(int First_TeamID) { int Match_ID = get_MatchID();

       int matchId;
       int teamID;
       int PlayerID;
       String StrikerName;
        if(Player_SD != null)
            Player_SD.clear();

       String selectQuery = "SELECT "+STMatchID+","+ STFristTeamID+","+ STStrikerPlayerID +","+ STStrikerPlayerName+", count "+"("+STStrikerPlayerName+")"+" FROM " 
                                     +TABLE_SCORE+" where "+ STMatchID +" = '"+ Match_ID +"'"+" AND "
                                     +STFristTeamID + "= '"+First_TeamID+"'"+" GROUP BY "+STStrikerPlayerName;

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

       if (cursor.moveToFirst()) {
            do {
                Player_score_details psd = new Player_score_details();

                matchId = cursor.getInt(cursor.getColumnIndex(STMatchID));
                psd.MTMID = cursor.getInt(cursor.getColumnIndex(STMatchID));

                teamID = cursor.getInt(cursor.getColumnIndex(STFristTeamID));                               
                psd.MTID = cursor.getInt(cursor.getColumnIndex(STFristTeamID));  

                PlayerID = cursor.getInt(cursor.getColumnIndex(STStrikerPlayerID));  
                psd.MTPID = cursor.getInt(cursor.getColumnIndex(STStrikerPlayerID));  

                StrikerName = cursor.getString(cursor.getColumnIndex(STStrikerPlayerName));  
                psd.PlayerName = cursor.getString(cursor.getColumnIndex(STStrikerPlayerName)); 

                Player_SD.add(psd);

            } while (cursor.moveToNext());
        }

        db.close();
}

Answer 2

you can use following

/**
     * Selects records from table
     * 
     * @param table
     *            = name of table
     * @param columns
     *            = String[] for columns
     * @param where
     *            = WHERE condition
     * @param whereargs
     *            = arguments if where parameter is in prepared statement format
     * @param groupby
     *            = GROUP BY column(s)
     * @param having
     *            = HAVING condition
     * @param orderby
     *            = ORDER BY column witrh asc, desc specification
     * @return
     */
    public List<Object[]> select(String table, String columns[], String where,
            String whereargs[], String groupby, String having, String orderby)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.query(table, columns, where, whereargs,
                groupby, having, orderby);      
        List<Object[]> list = parseCursorToList(cursor);
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        return list;
    }