Android / SQLite - Distinct count across multiple columns

Question

I have the following code...

protected long getNumQueuedChunks(boolean distinctEntries) throws Exception {

SQLiteDatabase db = null;
try {

    db = dbhelper.getReadableDatabase();

    String whereClause = C_STATUS + " = ? AND " + C_NUM_FAILURES + " < ?";
    String[] whereArgs = new String[] {STATUS_AWAITING_PROCESSING, 10};

    long count = DatabaseUtils.queryNumEntries(db, QUEUE_TABLE_NAME, whereClause, whereArgs);           
    return count;
}
finally {
    try {
        db.close();
    }
    catch(Exception ignore) {
    }
}

}

...which works fine if I want to return the total amount of rows that match the WHERE condition.

However, I would like to only count records that have distinct/unique combinations of data across these 3 columns: C_URI, C_BYTE_START_NUM and C_NUM_BYTES.

I know I could do something like...

    String[] columns = {C_URI, C_BYTE_START_NUM, C_NUM_BYTES};
String whereClause = C_STATUS + " = ? AND " + C_NUM_FAILURES + " < ?";
String[] whereArgs = new String[] {STATUS_AWAITING_PROCESSING, "10"};
Cursor c = db.query(true, QUEUE_TABLE_NAME, columns, whereClause, whereArgs, null, null, null, null);
int count = c.getCount();

...but I am hoping there is a more efficient way to perform a distinct count in this situation??

Just to add clarity, if I have this data in my table...

C_URI | C_BYTE_START_NUM | C_NUM_BYTES

1.jpg | 0 | 1024

1.jpg | 1024 | 1999

2.jpg | 0 | 500

...the result of the distinct count should be 3.

NB - I have seen a similar requirement described here (second answer) but that doesn't help me as I am wanting to do a distinct count across 3 columns rather than just one.

score 4 · Accepted Answer · edited Jul 04 '14 at 10:45

The most efficient way of counting records is to let the database do this:

SELECT COUNT(*)
FROM (SELECT DISTINCT Uri,
                      ByteStartNum,
                      NumBytes
      FROM QueueTable)

(With the separate subquery, it does not matter if you use DISTINCT or GROUP BY over the three columns.)

This query does not fit into the constraints of one of the helper functions like query or queryNumEntries, so you have to construct the entire SQL statement by hand:

long getNumQueuedChunks() {
    SQLiteDatabase db = dbhelper.getReadableDatabase();
    try {
        String query = "SELECT COUNT(*) FROM " +
                        "(SELECT DISTINCT " + C_URI + "," + C_BYTE_START_NUM + "," + C_NUM_BYTES +
                        " FROM " + QUEUE_TABLE_NAME +
                        " WHERE " + C_STATUS + " = ?" +
                        " AND " + C_NUM_FAILURES + " < 10)";
        String[] args = new String[] { STATUS_AWAITING_PROCESSING };
        return DatabaseUtils.longForQuery(db, query, args);
    } finally {
        db.close();
    }
}

score 1 · Answer 2 · answered Jul 04 '14 at 03:35

Have you tried the SQLiteDatabase.rawQuery() method? You can put a raw SQL query in it, for example, something like:

select distinct C_URI, C_BYTE_START_NUM, C_NUM_BYTES from MyTable

The method returns a Cursor, and you can immediately get the count from the cursor object. Of course, you can specify a where clause if you want to as well. Then free up the Cursor once you got your count.

Android / SQLite - Distinct count across multiple columns

2 Answers2