I saw the GroupBy parameter in the query method of the SQLiteDatabase class and wanted to know how it worked. I checked the Internet and couldn’t find anything. So I decided to play around with it myself until I got it working. Maybe this will help someone else…
We’re developing an Android App that helps individuals keep track of their Weight Watcher’s “Points” during the day (everything you eat has a point value and you try to keep your total points under a certain number for the day). We have a table with a row for every item you eat with its corresponding point value. The table also has a userId column in case more than one user might end up in the database.
We want to total the person’s points.
We query out all the rows for the individual into a cursor and then iterate through the cursor adding each row’s points to a previous total.
We add a GroupBy clause in our query method to aggregate the data. This way we don’t have to iterate through the cursor. In our case only one row is returned and it contains our total points.
OK, a few things…
First, you will notice that we included an aggregate function (in this case “Sum”) in the column listing. Next, we included the KEY_USERID column name in the GroupBy parameter. Lastly, we used the full "SUM(" + KEY_POINTS + ")" to find the index of that column, because that column’s name is now “sum(Points)” (the result of "SUM(" + KEY_POINTS + ")" )
You could, of course, just known that the Points were in the second column and have written it this way (remember it’s a zero based array):
A third way (and hopefully I’m not boring you by now, but someone might find it useful), is that you can alias your column that has the aggregate function like so:
While we are on the subject of SQL aggregate functions, I’ll note here that you can use all the standard SQL aggregate functions, not just “Sum”. So you can use your other favorites like “avg”, “count”, “max” and “min”.
Also, check out the explanations for “group_concat” and “total” on the SQLite site http://www.sqlite.org/lang_aggfunc.html