How to display Cursor count(something) return in the MainClass

Question

as you can see my code I would like to get the total number of specified student.

I have tried below code but I didn't get what I was finding!

//DatabaseHelper

public Cursor MyCredits(String StudentNo){
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery("SELECT COUNT(CourseCredit) FROM tblCourses JOIN tblMyCourses ON tblCourses.CourseNumber = tblMyCourses.CourseNumber WHERE StudentNumber = ? ",new String[]{ StudentNo } );
    return cursor;
}

//Main

String StudentNo = commonValues.SaveStudentID)
        Cursor cursor1 = db.MyCredits(StudentNo);
        if (cursor1.getCount() > 0){
            while (cursor1.moveToNext()){
                Log.i(TAG, cursor1.getString(0));
            }
        }else{
            Toast.makeText(context, "You didn't selected any Course", Toast.LENGTH_SHORT).show();
        }

The user I am trying to get his total number of credits is selected three courses: 2.5, 5, 2 credits. I expect the output of Count(CourseCredit) to be 9.5 but the actual output is 2.5.

Answer 1

You probably want to base your query on, the SUM of the column for the selected rows rather than the COUNT of the selected rows :-

Cursor cursor = db.rawQuery("SELECT sum(CourseCredit) FROM tblCourses JOIN tblMyCourses ON tblCourses.CourseNumber = tblMyCourses.CourseNumber WHERE StudentNumber = ? ",new String[]{ StudentNo } );

In you Database Helper you could use (to replace your current MyCredits method) :-

public double MyCredits(String StudentNo){

    double rv = 0;
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery("SELECT sum(CourseCredit) AS credits FROM tblCourses JOIN tblMyCourses ON tblCourses.CourseNumber = tblMyCourses.CourseNumber WHERE StudentNumber = ? ",new String[]{ StudentNo } );
    if (cursor.moveToFirst()) {
        rv = cursor.getDouble(cursor.getColumnIndex("credits"));
    }
    cursor.close();
    return rv;
}

Noting that the return value would be 0, if there were no rows that matched the student number.

You could then use something link the following in Main :-

double credits = db.MyCredits(commonValues.SaveStrudentID);
if (credits > 0) {
    Log.i(TAG, String.valueOf(credits));
} else {
    Toast.makeText(context, "You didn't selected any Course", Toast.LENGTH_SHORT).show()
}