Get a count of the number of times different entries appear in a database query (Android),

Question

I have a slightly complicated problem. My application enters user data into the SQLite database, and I was able to write queries to return the data.

This includes a query that receives a list of different values, an example might be as follows. Say you have the following data:

Column a | Column b | Column c aaa abb abb bbb 

Using the "group by" function, the query will create the following list:

 aaa abb bbb 

So what I'm trying, but I can’t do, is expand it and get the number of times how many times each individual record happens, so in the above case, it would look like this:

 1 x aaa 2 x abb 1 x bbb 

So, there is a way, as soon as you query the database, to get the count of each individual record, from the cursor - or I need to write a separate query to get this information.

Here is my request when he is looking 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 more information, here is the code in my main action, 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 also don’t know how to get the graph as soon as I included this bit of code in my request (which I still don’t see how it is done!).

Answer 1

Have you tried adding the COUNT(*) function to the column list?

see this: count a group of columns .

Answer 2

The request you want:

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

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

Answer 3

Try it.

Get all the unique values ​​and temporarily save them somewhere and just run a loop and run another query to try to find these values ​​again and every time you find them, update some counter.