title: Way to group sqlite cursor results by formated date column
body: <p>Is there a simpler way to group and present data from an SQLite database than what I'm about to propose? If so, how would you improve it?</p>
  
  <p>In an app I'm doing for a gym, I need to be able to record the history for 5 types of exercise classes, by date, in descending order (newest on top).    In the spirit of sharing code to better understand, here is my table creation:</p>
  
  <pre><code>private static final String CREATE_TABLE_BJJ = "CREATE TABLE IF NOT EXISTS "
      // TODO finish this
      + TABLE_BJJ + "(" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
      + BJJ_HISTORY_MOVE + " TEXT NOT NULL, " + BJJ_HISTORY_MOVECOUNT + " INTEGER NOT NULL, "
      + BJJ_HISTORY_PERFORMEDBY + " TEXT NOT NULL, " + BJJ_HISTORY_PERFORMEDTO
      + " TEXT NOT NULL, " + DATE + " datetime NOT NULL, " + UPLOADED
      + " INTEGER NOT NULL DEFAULT 0);";
  </code></pre>
  
  <p>this results in something like this once data gets recorded:
  <img src="https://i.stack.imgur.com/RzkGn.png" alt="tablerow"></p>
  
  <p>for the history page though, I need to somehow group all moves performed by date into one row with a sum of the number of moves performed, as in the screenshot below:</p>
  
  <p><img src="https://i.stack.imgur.com/oqaW4.png" alt="sample screen"></p>
  
  <p><strong>What I was thinking might work would be to populate an array with the results of a</strong> <a href="https://stackoverflow.com/questions/7403224/android-sqlite-how-to-select-distinct-valuse-from-1-column-in-table">SELECT DISTINCT STATEMENT</a> <strong>then filling an array with the results like so:</strong></p>
  
  <pre><code>String query ="SELECT DISTINCT Date FROM BJJHistory";
  ArrayList&lt;String&gt; dateList = new ArrayList&lt;String&gt;();
  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
  Cursor  cursor = database.rawQuery(query,null);
  if (cursor != null) {
      cursor.moveToFirst();
      if (!cursor.isAfterLast())
      {
           do
           {
                String datestr = sdf.format(new Date(cursor.getString(0))); 
                dateList.add(datestr);
           }
           while (cursor.moveToNext());
      }
  
  }
  return cursor;
  </code></pre>
  
  <p><strong>I have a custom class to hold the results:</strong></p>
  
  <pre><code>Public class History {
  public String numMoves;
  public String date;
  
  public History() {
      super();
  }
  
  public History(final String numMoves, final String date) {
      super();
      this.numMoves = numMoves;
      this.date = date;
      }
  }
  </code></pre>
  
  <p><strong>Afterward, using the array to then create cursors to pull data back as such:</strong></p>
  
  <pre><code>final ArrayList&lt;History&gt; BJJHistory = new ArrayList&lt;History&gt;();
  for (int i = 0; dateList.size(); i++) {
      final Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM BJJHistory WHERE Date LIKE '" + dateList(i).toString() + "%'") &gt; 0, null); 
      if (cursor != null) {
          cursor.moveToFirst();
  
          BJJHistory.add(new History(cursor.getString(0).toString(),dateList[i].toString())) 
      }
  </code></pre>
  
  <p><em>lot of typing</em></p>
  
  <p>Finally, using an array adapter to fill the listview with the resulting arraylist of history items.  I would still need to use a way to implement an onclick listener that would query the database for all the items for each date, so the user could see what moves they did that date, but the main listview is more important right now.</p>
  
  <p>As always,  Thanks in advance!</p>