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

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

1 Answer
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.

SELECT sum(AMOUNT) FROM people_table;

COL4

"AMOUNT"

0

sum(AMOUNT)

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

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

amo_sum

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



Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.