Get all numeric strings from SQLite Column and get total sum calculation

Question

I am new in Android and in SQLite. I have a column "AMOUNT" of just numbers in SQLite which I am able to show in a ListView. But I can't get any way that I understand to add them all and show in a TextView.

Here is the database Helper

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "people.db";
    private static final String TABLE_NAME = "people_table";
    private static final String COL1 = "ID";
    private static final String COL2 = "DATE";
    private static final String COL3 = "DESCRIPTION";
    private static final String COL4 = "AMOUNT";


    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                " DATE TEXT, DESCRIPTION TEXT, AMOUNT TEXT)";
        db.execSQL(createTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    public boolean addData(String inc_date, String inc_description, String inc_amount){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL2, inc_date);
        contentValues.put(COL3, inc_description);
        contentValues.put(COL4, inc_amount);

        long result  = db.insert(TABLE_NAME, null, contentValues);

        return result != -1;
    }

    public Cursor showData(){
        SQLiteDatabase db = this.getWritableDatabase();
        return db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
    }

    public boolean updateData(String id, String name, String email, String tvShow){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL1,id);
        contentValues.put(COL2,name);
        contentValues.put(COL3,email);
        contentValues.put(COL4,tvShow);
        db.update(TABLE_NAME, contentValues, "ID = ?", new String[] {id});
        return true;
    }

    public Integer deleteData(String id){
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(TABLE_NAME, "ID = ?", new String[] {id});
    }

}

And here is the Activity

import android.database.Cursor;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.widget.ArrayAdapter;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.TextView;

import java.util.ArrayList;

public class IncPag extends AppCompatActivity {

    DatabaseHelper peopleDB;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.inc_pag);
        all_inc_dat();
        amo_sum();
    }

    private void all_inc_dat(){
        TextView inc_data=findViewById(R.id.tex_inc);
        ListView dat_col=findViewById(R.id.dat_col);
        ListView des_col=findViewById(R.id.des_col);
        ListView amo_col=findViewById(R.id.amo_col);
        peopleDB=new DatabaseHelper(this);

        Cursor data = peopleDB.showData();

        if (data.getCount() == 0) {
            inc_data.setText(R.string.no_data_found);
            return;
        }
        ArrayList<String> dat_lis = new ArrayList<>();
        ArrayList<String> des_lis = new ArrayList<>();
        ArrayList<String> amo_lis = new ArrayList<>();

        while (data.moveToNext()) {
            dat_lis.add(data.getString(data.getColumnIndex( "DATE")));
            des_lis.add(data.getString(data.getColumnIndex( "DESCRIPTION")));
            amo_lis.add(data.getString(data.getColumnIndex( "AMOUNT")));

        }

        ListAdapter dat_ada = new ArrayAdapter<>(this, android.R.layout.simple_list_item_1, dat_lis);
        dat_col.setAdapter(dat_ada);
        ListAdapter des_ada = new ArrayAdapter<>(this, android.R.layout.simple_list_item_1, des_lis);
        des_col.setAdapter(des_ada);
        ListAdapter amo_ada = new ArrayAdapter<>(this, android.R.layout.simple_list_item_1, amo_lis);
        amo_col.setAdapter(amo_ada);
    }

    private void amo_sum(){
        TextView tv_sum=findViewById(R.id.tv_sum);
        tv_sum.setText(amount total?);
    }
}

If its easier to calculate within the Database helper then show me a SQLite solution. or if its easier to do calculation after getting on listview

Answer 1

Solution Part 1

You could add the following method to the database helper

public long getAmountSum(){
    long rv = 0;
    String[] columns = new String[]{"sum(" + COL4 + ")"};
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor csr = db.query(TABLE_NAME,columns,null,null,null,null,null);
    if (csr.moveToFirst()) {
        rv = csr.getLong(0);
    }
    csr.close();
    return rv;
}

• Note if a value in the AMOUNT column is not a valid integer then it will be given a value of 0 for that row.

• This equates to running the query SELECT sum(AMOUNT) FROM people_table; but uses the SQLiteDatabase query convenience method as opposed to using the rawQuery method.

  • It is considered better practice to use the convenience methods when they can be used, as opposed to using the rawQuery method.
  • It is also considered better practice to use a single source for table and column names rather than hard coding column names each time they are used (hence the use of COL4 rather than "AMOUNT").
  • Although using hard coded column offsets (e.g. 0 in the above), the above is an exception where the actual column name (i.e. sum(AMOUNT)), as it is derived from the underlying generated SQL, could be more problematic coding it than coding the column offset (first/only column so it will always be 0).

• If there are no rows in the table then 0 will be returned.

Solution Part 2

The above could be used in conjunction with an amended amo_sum method as per :-

private void amo_sum(){
    TextView tv_sum=findViewById(R.id.tv_sum);
    tv_sum.setText(String.valueOf(peopleDB.getAmountSum));
}

• Note the above is in-principle code and has not been tested so may contain some errors.