Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
1  
have you tried to make a raw query with something like this "SELECT SUM(builderNumber) FROM builders WHERE someColumn=x"? –  Desert Jul 16 at 22:25
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.