0

I have a bit of a tricky problem. My app inputs user data into the SQLite database and I have managed to write queries to get the data back out.

This includes a query that gets a list of distinct values, an example may be as follows. Say you have the following data:

Column a  |  Column b  | Column c
   a            a           a
   a            b           b
   a            b           b
   b            b           b

Using the 'group by' function, the query would create the following list:

aaa
abb
bbb

So what I am trying, but failing to do is expand this and get a count of the number of times each distinct entry occurs - so in the above case it would look like this:

1 x aaa
2 x abb
1 x bbb

So is there a way, once you have queried the database, to get a count of the occurancies of each distinct entry, from the Cursor - or do I need to write a seperate query to get this information..

Here is my query as it looks at the moment:

public Cursor fetchDamagedComponentSpecForInspection(long inspectionId, String componentType) {
    Cursor mCursor = rmDb.query(true, DAMAGED_COMPONENTS_TABLE, new String[] {
            DAMAGED_COMPONENT_ID,
            LOCATION_LINK,
            RUN_LINK,
            AREA_LINK,
            INSPECTION_LINK,
            LOCATION_REF,
            RACKING_SYSTEM,
            COMPONENT,
            COMPONENT_TYPE,
            QUANTITY,
            POSITION,
            RISK,
            ACTION_REQUIRED,
            NOTES_GENERAL,
            MANUFACTURER,
            TEXT1,
            TEXT2,
            TEXT3,
            TEXT4,
            NOTES_SPEC,
            SPEC_SAVED}, 
            INSPECTION_LINK + " = " + inspectionId + " AND " + COMPONENT_TYPE + " = ? AND " + SPEC_SAVED + " = ? ", 
            new String[] {componentType, "Yes"},
            MANUFACTURER + ", " + TEXT1 + ", " + TEXT2 + ", " + TEXT3 + ", " + TEXT4 + ", " + NOTES_SPEC,
            null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}

For additional information, here is the code in my main activity where I call the query and retrieve the data:

final Cursor componentsAndSpecCursor = (Cursor) rmDbHelper.fetchComponentsAndSpecForManufacturer(inspectionId, rackingSystem, manufacturer);
                if(componentsAndSpecCursor.moveToFirst()){
                    do {
                        componentType = componentsAndSpecCursor.getString(componentsAndSpecCursor.getColumnIndex(RMDbAdapter.COMPONENT_TYPE));
                        specText1 = componentsAndSpecCursor.getString(componentsAndSpecCursor.getColumnIndex(RMDbAdapter.TEXT1));
                        specText2 = componentsAndSpecCursor.getString(componentsAndSpecCursor.getColumnIndex(RMDbAdapter.TEXT2));
                        specText3 = componentsAndSpecCursor.getString(componentsAndSpecCursor.getColumnIndex(RMDbAdapter.TEXT3));
                        specText4 = componentsAndSpecCursor.getString(componentsAndSpecCursor.getColumnIndex(RMDbAdapter.TEXT4));
                        specNotes = componentsAndSpecCursor.getString(componentsAndSpecCursor.getColumnIndex(RMDbAdapter.NOTES_SPEC));
                        message.append(componentType).append(" ").append(specText1).append(" ").append(specText2).append(" ").append(specText3).append(" ").append(specText4).append(" ").append(specNotes).append("\r\n");
                    }
                    while (componentsAndSpecCursor.moveToNext());
                }
                componentsAndSpecCursor.close();

So I am also unaware of how I would get the Count once I have included this bit of code in my query (which I still can't see how it is done!).

Scamparelli
  • 756
  • 1
  • 12
  • 28
  • Can you provide an example of the code you are using for the `group by`? – Gordon Linoff Jan 05 '13 at 16:36
  • Hi Gordon, it is in the above query - see the line that states: MANUFACTURER + ", " + TEXT1 + ", " + TEXT2 + ", " + TEXT3 + ", " + TEXT4 + ", " + NOTES_SPEC, - this is the group by variable (you leave in null if you don't want to group by). – Scamparelli Jan 05 '13 at 17:26

3 Answers3

1

Did u try adding COUNT(*) function to your columns list?

see this: count columns group by

Community
  • 1
  • 1
Ovi
  • 60
  • 8
  • Hi Ovi, thanks for suggestion. I did see this but I can't work out where I should I add this into my query.. – Scamparelli Jan 05 '13 at 16:20
  • Ahh yes i was thinking about this. Scamparelli, have a look at the group by and count sql commands – Jono Jan 05 '13 at 16:41
  • Thanks for reply, but I'm still at a loss to where in the database.query statement I am using you would stick the COUNT(*) bit.. – Scamparelli Jan 05 '13 at 18:45
  • 1
    You can pass `"Count(*)"` in the selection array `new String[] { "Count(*)", DAMAGED_COMPONENT_ID, LOCATION_LINK, etc` or pass Gordon Linoff's query as a String to [`rawQuery()`](http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#rawQuery%28java.lang.String,%20java.lang.String[]%29) – Sam Jan 05 '13 at 20:11
1

The query that you want is:

select count(*) as cnt, cola, colb, colc
from YourTable
group by cola, colb, colc

I'm not sure how to put this into your code. You don't seem to have a query that returns the combinations in the snippet of code that you provided.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Try this.

Get all unique values and temporarily store them somewhere and just loop and do another query to try and find those values again and every time u find one, update some counter.

Jono
  • 17,341
  • 48
  • 135
  • 217
  • Hi Jonney, thanks for the quick response. I'm sure this is basic stuff but I haven't done this before - should I use an array to do this and if so is there a correct method of doing this? – Scamparelli Jan 05 '13 at 16:24
  • Use a List and create a pojo class of the data you want to store with getters and setters and just add the distinct data objects into your List and then after that you can do the above – Jono Jan 05 '13 at 16:43