CODEWITHSUNDEEP
androiddatabasesqlite
Master Zangetsu
Master Zangetsu

Reputation: 262

Android get sum of database column where value is like

In the simplest terms if I have a table with values as follows

x    5
x    7
y    3
v    19
x    24

I wish to add up all the int values of x excluding those of y and v etc

I currently have working code that return all column values of x (in this case dateAccessed)

public ArrayList<HashMap<String, String>> searchByDate() {


    String dateAccessed = FoodDiary.dateAccessed;       

    ArrayList<HashMap<String, String>> wordList;
    wordList = new ArrayList<HashMap<String, String>>();
    SQLiteDatabase database = this.getReadableDatabase();
    //String selectQuery = "SELECT  * FROM diarys";
    String selectQuery = "diarys";
    Cursor cursor = database.query(selectQuery, new String[] { "diaryId",
            "diaryName", "diaryDate", "diaryNumber" }, "diaryDate LIKE '"
            + dateAccessed + "%'", null, null, null, null);
    if (cursor.moveToFirst()) {
        do {
            HashMap<String, String> map = new HashMap<String, String>();
            map.put("diaryId", cursor.getString(0));
            map.put("diaryName", cursor.getString(1));
            map.put("diaryDate", cursor.getString(2));
            map.put("diaryNumber", cursor.getString(3));
            wordList.add(map);
        } while (cursor.moveToNext());
    }
    return wordList;

}

and I also have working code for returning the SUM of an entire column

int total = 0;
    Cursor cursor = database.rawQuery("SELECT SUM(builderNumber) FROM builders", null);

    if (cursor.moveToFirst()) 
    {
        total = cursor.getInt(0);
    } while (cursor.moveToNext());
    return total;

I cannot however seem to combine the two to get them to both work together. Whatever way I construct the cursor it always fails.

Any help with this would be greatly appreciated

Upvotes: 1

Views: 1544

Answers (1)

Peter Birdsall
Peter Birdsall

Reputation: 3425

IF your doing a raw query for your simple example in the begining

select sum(dairyNumber) from yourtable where dairyId = 'x'

  or, if you want another column than just the sum .

select diaryName, sum(dairyNumber) from yourtable where dairyId = 'x'

If you want to pass the value of 'x' as a parameter then you can use the ? and supply a where argument.

For your specific example your raw query should be something like 

select sum(diaryNumber) from yourtable where diaryDate like '201307%'

select diaryName, sum(diaryNumber) from yourtable where diaryDate like '201307%'

Hope this helps.

Upvotes: 1

Related Questions