CODEWITHSUNDEEP
androidsqlite
Logic1
Logic1

Reputation: 1847

How to count the number of duplicate rows but return only the values of distinct in SQLite?

I am trying to use SQLite's count() function to calculate the quantity of items for each distinct item in a store's inventory.

I have started off by creating an "inventory" table where each row represent different store locations.

Table of storefronts:

db.execSQL("CREATE TABLE IF NOT EXISTS stores (" +
        "_id              INTEGER            PRIMARY KEY AUTOINCREMENT, " +
        "timestamp        DATETIME           DEFAULT CURRENT_TIMESTAMP " +
        //many other columns (eg. address, state, phone...)
        ");");

I then created a table which contains a list of all the items where _store_id is basically pointing to the primary key in the "stores" table.

Table of item barcode scan information:

db.execSQL("CREATE TABLE IF NOT EXISTS item_scans (" +
        "_id              INTEGER            PRIMARY KEY AUTOINCREMENT, " +
        "_store_id        INTEGER," +
        "item_code        VARCHAR " +
        ");");

Attempt to retrieve inventory information for a specific store:

To start I tried a simple query to get a list of all items that have been scanned relating to a particular storefront (the variable sid is used to select the store's primary key)

Query:

Cursor cursor = db.rawQuery("SELECT _store_id, item_code " +
                " FROM item_inventory" + 
                " WHERE _store_id = " + String.valueOf(sid),
        null);

Which produces a result where all items (including duplicates) are returned.

Result:

 0 {
    _store_id=14
    item_code=852874006301
 }
 1 {
    _store_id=14
    item_code=852874006301
 }
 2 {
    _store_id=14
    item_code=041570110461
 }

I then tried to count the number of occurrences of each item and return results containing only distinct item_code rows, but I also include a new column called item_inventory which should be a count of the quantity of how many identical item_code entries were present for the given sid.

Query:

Cursor cursor = db.rawQuery("SELECT DISTINCT t1._store_id, t1.item_code, t2.item_quantity " +
                " FROM item_invnetory t1 " +
                " INNER JOIN (SELECT _store_id, item_code, count(item_code) AS item_quantity " +
                        " FROM item_inventory" +
                        " WHERE _store_id = " + String.valueOf(sid) +
                ") t2 " +
                " ON t1._store_id = t2._store_id " +
                " WHERE t1._store_id = " + String.valueOf(sid),
        null);

This gives me a list of distinct items but the value for item_inventory is incorrect and appears as though its calculating the total quantity of all items for a particular store:

Result:

 0 {
    _store_id=14
    item_code=041570110461
    item_quantity=3
 }
 1 {
    _store_id=14
    item_code=852874006301
    item_quantity=3
 }

What I really was hoping to achieve was to use an SQLite query where item_inventory was the value of the total number of items for each distinct item at a store location.

Example of desired result:

 0 {
    _store_id=14
    item_code=041570110461
    item_quantity=1
 }
 1 {
    _store_id=14
    item_code=852874006301
    item_quantity=2
 }

I apologize for my lack of experience with SQLite, I do not often get to use inner join and/or distinct nor do I use much of raw queries in general but my boss asked me to create a project using Android and display everything in a ListView.

Upvotes: 0

Views: 2249

Answers (2)

Arman Reyaz
Arman Reyaz

Reputation: 437

I have resolved the same problem using the below code:

String query = "SELECT violationDate, COUNT(*) as date " +
            "FROM challan " +
            "WHERE challanType = '" + type + "' GROUP BY violationDate";

Here violationDate and date are two columns of the result table. date column will return occurrence.

cr.getInt(1)

Upvotes: 0

kennytm
kennytm

Reputation: 523394

You use the GROUP BY clause for this, no need to use JOIN or DISTINCT.

SELECT item_code, COUNT(*) as item_quantity
FROM item_inventory
WHERE _store_id = ?
GROUP BY item_code

Upvotes: 2

Related Questions