Using GroupBy in the SQLiteDatabase query method

Problems with WiFi, SQLite ,Bluetooth, WiMax, Proxies, etc...

Using GroupBy in the SQLiteDatabase query method

Postby kennyjacobson » Sat Nov 01, 2008 9:37 pm

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…

Background
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.

Problem
We want to total the person’s points.

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

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2. /*
  3.  
  4. *
  5.  
  6. * NOTE: mDb below is a SQLiteDatabase object.
  7.  
  8. *
  9.  
  10. * For this example we are assuming the mDb was
  11.  
  12. * created and instantiated previously.
  13.  
  14. *
  15.  
  16. */
  17.  
  18.  
  19.  
  20. String DATABASE_TABLE = “myTable”;  //Table name in database
  21.  
  22. String KEY_USERID = “UserID”;       //Column Name in database
  23.  
  24. String KEY_POINTS = “Points”;       //Column Name in database
  25.  
  26.  
  27.  
  28. double totalPoints = 0;
  29.  
  30. String userID = “ABC123”;
  31.  
  32.        
  33.  
  34. Cursor c = mDb.query(DATABASE_TABLE,
  35.  
  36.                      new String[] {KEY_USERID,KEY_POINTS},
  37.  
  38.                      KEY_USERID + "='" + userID + "'",
  39.  
  40.                      null,
  41.  
  42.                      null,
  43.  
  44.                      null,
  45.  
  46.                      null);
  47.  
  48.  
  49.  
  50. while(c.moveToNext()){
  51.  
  52.         totalPoints += c.getDouble(c.getColumnIndex(KEY_POINTS));      
  53.  
  54. }
  55.  
  56.  
Parsed in 0.033 seconds, using GeSHi 1.0.8.4


Solution 2
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.

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2. Cursor c = mDb.query(DATABASE_TABLE,
  3.  
  4.                    new String[] {KEY_USERID, "SUM(" + KEY_POINTS + ")"},
  5.  
  6.                    KEY_USERID + "='" + userID + "'",
  7.  
  8.                    null,
  9.  
  10.                    KEY_USERID, //Our GroupBy Clause
  11.  
  12.                    null,
  13.  
  14.                    null);
  15.  
  16. c.moveToFirst();
  17.  
  18. totalPoints = c.getDouble(c2.getColumnIndex("SUM(" + KEY_POINTS + ")"));
Parsed in 0.033 seconds, using GeSHi 1.0.8.4


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):

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. totalPoints = c.getDouble(1);
Parsed in 0.035 seconds, using GeSHi 1.0.8.4


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:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2. Cursor c = mDb.query(DATABASE_TABLE,
  3.  
  4.                    new String[] {KEY_USERID, "SUM(" + KEY_POINTS + ") as KennyRocks"},
  5.  
  6.                    KEY_USERID + "='" + userID + "'",
  7.  
  8.                    null,
  9.  
  10.                    KEY_USERID, //Our GroupBy Clause
  11.  
  12.                    null,
  13.  
  14.                    null);
  15.  
  16. c.moveToFirst();
  17.  
  18. totalPoints = c.getDouble(c2.getColumnIndex("KennyRocks"));
Parsed in 0.038 seconds, using GeSHi 1.0.8.4


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”.

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. Cursor c = mDb.query(DATABASE_TABLE, new String[] {KEY_USERID, "AVG(" + KEY_POINTS + ")"}, …
Parsed in 0.036 seconds, using GeSHi 1.0.8.4


or...

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. Cursor c = mDb.query(DATABASE_TABLE, new String[] {KEY_USERID, "MAX(" + KEY_POINTS + ")"}, …
Parsed in 0.037 seconds, using GeSHi 1.0.8.4


etc.

Also, check out the explanations for “group_concat” and “total” on the SQLite site http://www.sqlite.org/lang_aggfunc.html
User avatar
kennyjacobson
Freshman
Freshman
 
Posts: 2
Joined: Fri Oct 31, 2008 7:54 pm
Location: Huntington Beach

Top

Return to Networking & Database Problems

Who is online

Users browsing this forum: No registered users and 5 guests