Android Sqlite calculation : How to Sum up two Columns sperately and divide one column sum by another column sum

Question

How guys According to my time zone i should be saying Good morning

Am working on an android project that will combine or join two tables and display by id and it worked i now want to know How to sum up two Columns sperately and divide one column sum by another column sum from that same function

My progress so far

  public List<Courses> getListCourses(int id) {
    Courses courses = null;
    List<Courses> coursesList = new ArrayList<>();
    openDatabase();

    Cursor cursor = mDatabase.rawQuery("SELECT * FROM COURSES C INNER JOIN SEMESTER S ON S.semester_id=C.semester_id AND S.LEVEL_ID = C.LEVEL_ID AND S.LEVEL_CODE = C.LEVEL_CODE WHERE C.semester_id =  ?", new String[]{String.valueOf(id)});

    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        courses = new Courses(cursor.getInt(0), cursor.getInt(1), cursor.getString(2), cursor.getInt(3), cursor.getInt(4), cursor.getString(5), cursor.getInt(6), cursor.getString(7), cursor.getInt(8));
        coursesList.add(courses);
        cursor.moveToNext();
    }
    cursor.close();
    closeDatabase();
    return coursesList;
}

These are the two tables

Semester table

   Id  |   semester_id    |   semester_code    |   level
   -------------------------------------------------
   1   |   1              |   1st Semester     |   100       
   2   |   2              |   2nd Semester     |   100        
   3   |   3              |   1st Semester     |   100       
   4   |   4              |   2nd Semester     |   100        
   5   |   5              |   1st Semester     |   100        

Courses Table

 Id  |Se_id| grade |  level |  semester     |  credit_unit  |  gp_unit
 ----------------------------------------------------------------------
 62  |  1  |  A   |  100   |  1st Semester  |  2           |  5
 63  |  1  |  B   |  100   |  1st Semester  |  2           |  4
 64  |  2  |  C   |  100   |  2nd Semester  |  2           |  3
 65  |  2  |  A   |  100   |  2nd Semester  |  2           |  5
 66  |  1  |  C   |  100   |  1st Semester  |  3           |  2

I want to sum up CREDIT_UNIT column and sum up GP_UNIT column and divide GP_UNIT sum up by CREDIT_UNIT sum up am stuck don't know how to add it to this

 Cursor cursor = mDatabase.rawQuery("SELECT * FROM COURSES C INNER JOIN SEMESTER S ON S.semester_id=C.semester_id AND S.LEVEL_ID = C.LEVEL_ID AND S.LEVEL_CODE = C.LEVEL_CODE WHERE C.semester_id =  ?", new String[]{String.valueOf(id)});

Answer 1

You can use sum method and then divide it by storing it in variables

int creditunit,gpunit;
cursor = db.rawQuery("select sum(credit_unit) from FROM COURSES C INNER JOIN SEMESTER S ON S.semester_id=C.semester_id AND S.LEVEL_ID = C.LEVEL_ID AND S.LEVEL_CODE = C.LEVEL_CODE WHERE C.semester_id =  ? ;", new String[]{String.valueOf(id)});
if(cursor.moveToFirst())
    creditunit =cursor.getInt(0);
else
    creditunit = -1;
cursor.close();

cursor = db.rawQuery("select sum(gp_unit) FROM COURSES C INNER JOIN SEMESTER S ON S.semester_id=C.semester_id AND S.LEVEL_ID = C.LEVEL_ID AND S.LEVEL_CODE = C.LEVEL_CODE WHERE C.semester_id =  ? ;", new String[]{String.valueOf(id)});
if(cursor.moveToFirst())
    gpunit =cursor.getInt(0);
else
    gpunit = -1;
cursor.close();
//perform division
double sol= creditunit/gpunit;

EDIT: The query is ,

 cursor = db.rawQuery("select sum(credit_unit),sum(gp_unit) from FROM COURSES C INNER JOIN SEMESTER S ON S.semester_id=C.semester_id AND S.LEVEL_ID = C.LEVEL_ID AND S.LEVEL_CODE = C.LEVEL_CODE WHERE C.semester_id =  ? ;", new String[]{String.valueOf(id)});

Update

To access the code of adapter class in your activity,

Make your task as a method in activity,

public void getsolution(double sol)
{
    //code for displaying sol in textview
}

Then in your adapter class,

1.pass the activity reference in constructor

public adapter(YourActivity activity)

2.After executing your sql code, call the activity method

 activity.getsolution(sol);