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

Question

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.

Why the MySQL tag? Please provide the original sample data that would produce desired output. (Also see How to format SQL tables in a Stack Overflow post?) — CL.
– CL., Commented Feb 23, 2017 at 8:27
Total typo on the MySQL tag! Thanks for pointing that out. Also thanks for the tip on SQL table formatting!! — Logic1
– Logic1, Commented Feb 23, 2017 at 8:30

CL. · Accepted Answer · 2017-09-18 07:52:21Z

2

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

edited Sep 18, 2017 at 7:52

CL.

182k18 gold badges241 silver badges282 bronze badges

answered Feb 23, 2017 at 8:37

kennytm

526k111 gold badges1.1k silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Logic1 Over a year ago

Perfect!! Thank you so much! I love StackOverflow

Arman Reyaz · Accepted Answer · 2021-02-17 07:20:24Z

0

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)

answered Feb 17, 2021 at 7:20

Arman Reyaz

4371 gold badge5 silver badges9 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related