SQLite Not Display duplicates and count how many times a common value for a row appears

Question

In my android application I have flowers that have a column called location which stores the city they are found. In my list view while returning the different cities I am receiving a separate row for each flower that contains a city location. Instead I would like to check if there are duplicates so that I only have one row that shows "Los Angeles" instead of 4. I then want to be able to derive the number of times that a particular city appears as a value in the column so I can display the number of times that it (In this case the number of flowers in this city) occurs in a textview.

I'm using a cursor to grab the data that I am using however not sure how to make such commands within the Android, SQlite interface of this.

Below is my cursor definition

public Loader<Cursor> onCreateLoader(int id, Bundle args) {
    switch (id) {
        case LOADER_ID:
            return new CursorLoader(
                    getContext(),
                    DatabaseDescription.Flower.CONTENT_URI,
                    FROM_COLUMNS,
                    COLUMN_LOCATION + "<> ''",
                    null,
                    COLUMN_LOCATION + " ASC"
            );
        default:
            if (BuildConfig.DEBUG)
                throw new IllegalArgumentException("no id handled!");
            return null;
    }
}

I doubt you need to see my adapter as the Cursor is responsible for the content that I receive. Most important is to know how to not display duplicates and how to count number of rows that have the same value in the particular column. Appreciate the assistance.

score 0 · Answer 1 · answered Jan 25 '18 at 22:05

0

You have two things you are trying to do. You will probably need to execute your query twice.

To only get once row per city you can use the DISTINCT keyword on your column for your city name.

To get the count of the rows for a city you can get the cursor size from the cursor that is returned for your query.

answered Jan 25 '18 at 22:05

dazza5000

7,075
9
44
89

The ContentProvider has a selection parameter that is essentially a WHERE clause built into it. It is the fourth parameter and mine above says where the value is not null. The trick is that the format does not allow for simply inserting a full SQL command there. – NVA Jan 25 '18 at 22:36
You can use LIMIT 1 at the end of your ASC clause to get one row per city and the cursor.size() to determine the count. – dazza5000 Jan 25 '18 at 22:45
LIMIT 1 seems to be a separate command which according to my attempts seem would have to be a different parameter. If you have a suggestion for ways to write the statement, Im open to your input. – NVA Jan 25 '18 at 22:56
https://stackoverflow.com/questions/9898043/how-to-add-limit-clause-using-content-provider – dazza5000 Jan 25 '18 at 22:58
The Limit approach doesn't return every unique value in the column. Instead it simply limits the number of values that can be displayed in the list. Perhaps mentioning contentprovider in my question will help clarify the approach to the solution too. – NVA Jan 26 '18 at 01:07
I apologize. I must not understand the problem. – dazza5000 Jan 26 '18 at 01:09
Thanks for the positive direction. – NVA Jan 26 '18 at 01:29

SQLite Not Display duplicates and count how many times a common value for a row appears

1 Answers1