mardi 14 avril 2015

How to sum up and get totals of all numeric values in one column in android SQLite

So i have created and filled my database table,displayed data using custom adapter in an activity.I have tried to add the values of one column "amount" and getting the total displayed in an activity.After research,this is what i found suited my goal:



public int getTotalOfAmount(){
SQLiteDatabase db = this.getReadableDatabase();
Cursor c=db.rawQuery("SELECT SUM(amount)FROM"+CONTACTS_TABLE_NAME,null);
c.moveToFirst();
int i=c.getInt(0);
c.close();
return i;
}


And displaying in my activity as:



TextView txt2 = (TextView) findViewById(R.id.Amount_textView);
int i=myDb.getTotalOfAmount();
txt2.setText(""+i);


However on running the app,it crashes when the activity is started with the following logcat error:



04-14 13:14:13.513 9231-9231/com.snappy.stevekamau.cosmeticsapp E/SQLiteLog﹕ (1) no such column: amount
04-14 13:14:13.603 9231-9231/com.snappy.stevekamau.cosmeticsapp W/dalvikvm﹕ threadid=1: thread exiting with uncaught exception (group=0x40e83438)


Now i dont know what is happening here because i do have this column CONTACTS_COLUMN_AMOUNT = "amount";in my table.From what i understand,there must be something i have missed on my code. Complete DBHelper.java:



public class DBHelper extends SQLiteOpenHelper {

public static final String DATABASE_NAME = "MyDBName.db";
public static final String CONTACTS_TABLE_NAME = "contacts";
public static final String CONTACTS_COLUMN_ID = "id";
public static final String CONTACTS_COLUMN_TITLE = "title";
public static final String CONTACTS_COLUMN_AMOUNT = "amount";
public static final String CONTACTS_COLUMN_DESC = "description";

private HashMap hp;

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

@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL(
"create table contacts " +
"(id integer primary key, title text,amount text,description text)"
);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("DROP TABLE IF EXISTS contacts");
onCreate(db);
}

public boolean insertContact(String title, String amount, String description) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();

contentValues.put("title", title);
contentValues.put("amount", amount);
contentValues.put("description", description);


db.insert("contacts", null, contentValues);
return true;
}

public Cursor getData(int id) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor res = db.rawQuery("select * from contacts where id=" + id + "", null);
return res;
}

public int numberOfRows() {
String countQuery = "SELECT * FROM " + CONTACTS_TABLE_NAME;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
int cnt = cursor.getCount();
cursor.close();
return cnt;
}

public boolean updateContact(Integer id, String title, String amount, String description) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("title", title);
contentValues.put("amount", amount);
contentValues.put("description", description);

db.update("contacts", contentValues, "id = ? ", new String[]{Integer.toString(id)});
return true;
}

public void deleteContact() {
SQLiteDatabase db = this.getWritableDatabase();
db.delete("contacts", null, null);
db.close();
}
public void deleteSingleContact(int id) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(CONTACTS_TABLE_NAME, CONTACTS_COLUMN_ID+"="+id,null);

}


public boolean checkForTables() {
boolean hasRows = false;
SQLiteDatabase db = getReadableDatabase();
Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + CONTACTS_TABLE_NAME, null);
cursor.moveToFirst();
int count = cursor.getInt(0);
if(count > 0)
hasRows = true;
db.close();
return hasRows;
}
public int getTotalOfAmount(){
SQLiteDatabase db = this.getReadableDatabase();
Cursor c=db.rawQuery("SELECT SUM(amount)FROM"+CONTACTS_TABLE_NAME,null);
c.moveToFirst();
int i=c.getInt(0);
c.close();
return i;
}

public ArrayList<ContactListItems> getAllContacts() {
ArrayList<ContactListItems> contactList = new ArrayList<>();
hp = new HashMap();
SQLiteDatabase db = this.getReadableDatabase();
Cursor res = db.rawQuery("select * from contacts", null);
res.moveToFirst();
while (!res.isAfterLast()) {
ContactListItems contactListItems = new ContactListItems();

contactListItems.setTitle(res.getString(res
.getColumnIndex("title")));
contactListItems.setAmount(res.getString(res
.getColumnIndex("amount")));
contactListItems.setDescription(res.getString(res
.getColumnIndex("description")));
contactList.add( contactListItems);
res.moveToNext();
}
res.close();
return contactList;
}
}


To Note: Also i should add that the Amount column is a string input type,i am not sure if it should be an integer for the above method to work??Any assistance will be greatly appreciated


Aucun commentaire:

Enregistrer un commentaire