codeme2020 codeme2020 - 3 months ago 13
Android Question

SQlite: Query to find the total of all values in a column?

I am trying to add a functionality to my app that allows the user to view the average of their scores from all the game sessions they have played.

In order to do so I need to find the total of all values in a column within the Database.

This is my current attempt:

Query Method:

/**
* Method that gives the the total of all
* Average Meditation levels in the DB
* @return
*/
public Cursor getTotalOfAllMedLevels(){

SQLiteDatabase db = this.getWritableDatabase();

String query = "SELECT SUM(avgmeditation) FROM " + TABLE_SCORE;


Cursor c = db.rawQuery(query, null);

//Add in the movetofirst etc here? see SO
c.moveToFirst();
c.getInt(0);

return c;



}


Attempting to implement it, setting cursor equal to its return value in textview:

public void displayAverageOfAllMedValues() {

//Setting cursor to return value of the method?
Cursor c = db.getTotalOfAllMedLevels();

if( c != null && c.moveToFirst() )
{

avgMed.setText("" + c.getInt( 0 ) );

}

}


This just gives me the answer
0
at the minute which I know isn't correct, What am I doing wrong?

The table structure (for reference):

public class DatabaseHelper extends SQLiteOpenHelper {

// 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);

}


EDIT:

When trying to implement the following code I am getting a NullPointerException related to the displayAverageOfAllAttValues() method.

Updated Activity:

public class thirdActivity extends ActionBarActivity {

TextView count;
TextView avgMed;
TextView avgAtt;

DatabaseHelper db = new DatabaseHelper(this);


@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.summary);

initialiseVars();

displayAllResults();



}

public void initialiseVars(){

count= (TextView) findViewById(R.id.tvSummary1);
avgMed= (TextView) findViewById(R.id.tvSummary2);
avgAtt= (TextView) findViewById(R.id.tvSummary3);

}

//Facade method that then calls all the rest
public void displayAllResults(){

displayNumberofGamesPlayed();

displayAverageOfAllMedValues();

displayAverageOfAllAttValues();
}

public void displayNumberofGamesPlayed(){

//show how many games have been played
int totalGamesPlayed = db.getTotalGamesPlayed();
count.setText("games played " + totalGamesPlayed);

}

public void displayAverageOfAllMedValues() {

//Setting cursor to return value of the method?
int i = db.getTotalOfAllAvgMedLevels();
avgMed.setText("Total of med" + i );

}

public void displayAverageOfAllAttValues() {

//Setting cursor to return value of the method?
int i = db.getTotalOfAllAvgAttLevels();
avgAtt.setText("Total of att" + i );

}



}


displayAverageOfAllAttValues() method in databaseHelper:

public int getTotalOfAllAvgAttLevels(){


SQLiteDatabase db = this.getWritableDatabase();

String query = "SELECT SUM(avgattention) FROM " + TABLE_SCORE;


Cursor c = db.rawQuery(query, null);

c.moveToFirst();
int i=c.getInt(0);

return i;

}


Logcat error report:

08-08 00:36:45.400: E/AndroidRuntime(10219): FATAL EXCEPTION: main
08-08 00:36:45.400: E/AndroidRuntime(10219): Process: com.example.brianapp, PID: 10219
08-08 00:36:45.400: E/AndroidRuntime(10219): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.brianapp/com.example.brianapp.thirdActivity}: java.lang.NullPointerException
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2305)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.app.ActivityThread.startActivityNow(ActivityThread.java:2140)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.app.LocalActivityManager.moveToState(LocalActivityManager.java:135)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.app.LocalActivityManager.startActivity(LocalActivityManager.java:347)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.widget.TabHost$IntentContentStrategy.getContentView(TabHost.java:749)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.widget.TabHost.setCurrentTab(TabHost.java:413)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.widget.TabHost$2.onTabSelectionChanged(TabHost.java:154)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.widget.TabWidget$TabClickListener.onClick(TabWidget.java:625)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.view.View.performClick(View.java:4633)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.view.View$PerformClick.run(View.java:19330)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.os.Handler.handleCallback(Handler.java:733)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.os.Handler.dispatchMessage(Handler.java:95)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.os.Looper.loop(Looper.java:157)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.app.ActivityThread.main(ActivityThread.java:5356)
08-08 00:36:45.400: E/AndroidRuntime(10219): at java.lang.reflect.Method.invokeNative(Native Method)
08-08 00:36:45.400: E/AndroidRuntime(10219): at java.lang.reflect.Method.invoke(Method.java:515)
08-08 00:36:45.400: E/AndroidRuntime(10219): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1265)
08-08 00:36:45.400: E/AndroidRuntime(10219): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1081)
08-08 00:36:45.400: E/AndroidRuntime(10219): at dalvik.system.NativeStart.main(Native Method)
08-08 00:36:45.400: E/AndroidRuntime(10219): Caused by: java.lang.NullPointerException
08-08 00:36:45.400: E/AndroidRuntime(10219): at com.example.brianapp.thirdActivity.displayAverageOfAllAttValues(thirdActivity.java:78)
08-08 00:36:45.400: E/AndroidRuntime(10219): at com.example.brianapp.thirdActivity.displayAllResults(thirdActivity.java:55)
08-08 00:36:45.400: E/AndroidRuntime(10219): at com.example.brianapp.thirdActivity.onCreate(thirdActivity.java:34)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.app.Activity.performCreate(Activity.java:5426)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1105)
08-08 00:36:45.400: E/AndroidRuntime(10219): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2269)
08-08 00:36:45.400: E/AndroidRuntime(10219): ... 18 more

Answer

There seems to be some error in code.See if doing the following changes work

public int getTotalOfAllMedLevels(){

    SQLiteDatabase db = this.getWritableDatabase();

    String query = "SELECT SUM(avgmeditation) FROM " + TABLE_SCORE;


    Cursor c = db.rawQuery(query, null);

    //Add in the movetofirst etc here? see SO
    c.moveToFirst(); 
    int i=c.getInt(0);

    return i;



}

public void displayAverageOfAllMedValues() {

//Setting cursor to return value of the method?
int i = db.getTotalOfAllMedLevels();
         avgMed.setText("" +i );
}