0

In my application I am receiving the DISTINCT value from rows that are in a column. I am looking to count how many items have this same value. An example would be

if I have an array {1,1,2,2,1,2,2,3}

I am seeking to derive the value of int 3 for distinct value 1 as it appears 3 times in the array.

The setting is I am using SQLiteDatabase in my Android Application.

I'm seeking to derive the information in my adapter so that I can present the count

my database helper makes this call called getGroupsList

   public ArrayList<String> getGroupsList(){
    ArrayList<String> allGroups = new ArrayList<>();

    String groupsList = "select DISTINCT " + Flower.COLUMN_GROUPS + " from " + Flower.TABLE_NAME;

    Cursor cursor =  getReadableDatabase().rawQuery(groupsList, null);
    if (cursor.getCount() > 0){
        cursor.moveToFirst();

        while(!cursor.isAfterLast()){
            allGroups.add(cursor.getString(0));
            cursor.moveToNext();
        }
    }

    cursor.close();

    return allGroups;
}

This retrieves the object that contains each Distinct value from the particular column if that helps to understand context. Your assistance is appreciated.

NVA
  • 1,662
  • 5
  • 17
  • 24

1 Answers1

1

You need to group by and do a count.

Select flower.id, count(*) from flower group by flower.id;

This is a similar issue:

How to use count and group by at the same select statement

dazza5000
  • 7,075
  • 9
  • 44
  • 89
  • To clarify, this statement is saying get the count of flower from the flower table and group by flowers? – NVA Feb 03 '18 at 05:43
  • This statement says group the entries by flower.id, do a count of the number of the entries in each group and output the flower.id and count of each group. – dazza5000 Feb 03 '18 at 14:27
  • This query seems like it would work. In the process of using a Cursor, I am not sure whether my method should expect an arraylist of the items (Flower) or Interger. So far I am not able to get the COUNT of members in each group using SQLite. I return the distinct items and the count of how long the name is. – NVA Feb 05 '18 at 22:23