How to get a count of each distinct value (SQLite Query - Android)

Question

I understand how to create a query to find only distinct values, but I want to count how many of each distinct value there are.

So an example of my code, in my database helper, which just gives me a list of distinct values:

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;
}

So this will return a list something like:

Dexion, M Duty, 3000mm, Blue
Hi-lo, Rackplan, 4000mm, Blue
PSS, P85 1.6, 4500mm, Blue

But I want to know how many of each value there were (i.e how many 'Dexion, M Duty, 3000mm, Blue' entries there were, so the output would look a bit like this:

2 x Dexion, M Duty, 3000mm, Blue
5 x Hi-lo, Rackplan, 4000mm, Blue
1 x PSS, P85 1.6, 4500mm, Blue

CommonsWare has suggested a post which offers the following solution:

select type, count(type) from table group by type;

However, I am unsure how to integrate this into my existing code with the type of query I am using.

EDIT - below is my attempt to integrate this count function with no success:

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 + 
                    ", Count (*) AS count", 
                    null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
}

Also can't understand how I actually retrieve this in my main code (below is how I get bits out at the moment):

        manufacturer = RMUtilities.notEmpty(cursor.getString(cursor.getColumnIndex(RMDbAdapter.MANUFACTURER)),"");
        text_1 = RMUtilities.notEmpty(cursor.getString(cursor.getColumnIndex(RMDbAdapter.TEXT1)),"");
        text_2 = RMUtilities.notEmpty(cursor.getString(cursor.getColumnIndex(RMDbAdapter.TEXT2)),"");
        text_3 = RMUtilities.notEmpty(cursor.getString(cursor.getColumnIndex(RMDbAdapter.TEXT3)),"");
        text_4 = RMUtilities.notEmpty(cursor.getString(cursor.getColumnIndex(RMDbAdapter.TEXT4)),"");
        spec_notes = RMUtilities.notEmpty(cursor.getString(cursor.getColumnIndex(RMDbAdapter.NOTES_SPEC)),"");

But how do I grab the count value?

Thanks in advance.

http://stackoverflow.com/questions/5522607/how-can-i-do-a-count-distinct-in-sqlite — CommonsWare, Dec 20 '12 at 20:43
Thanks CommonsWare, I did read that post. I'm just a bit confused on how I do it with the way I am writing the query (i.e. it's quite a big query and where do I place count(Distinct) and how do I grab this information?) — Scamparelli, Dec 20 '12 at 21:05

score 0 · Answer 1 · answered Dec 21 '12 at 16:42

0

From my code:

String w = myColumn + "like ?"
String [] args = { myData }
c = ourDatabase.query(DATABASE_TABLE, columns, w, args, null, null, null);
int count = c.getCount(c.getColumnIndex(myColumn));

answered Dec 21 '12 at 16:42

AndroidPenguin

3,445
2
21
42

Hi AndroidPenguin, I'm afraid I don't see how this would work with my type of query. Proper newbee here so apologies if I'm missing something really obvious. – Scamparelli Dec 21 '12 at 16:58

How to get a count of each distinct value (SQLite Query - Android)

1 Answers1