Android SQLiteDatabase.query() GROUP BY / HAVING With More Than One Column Not Working

In Android I am having a table which stores latitude and longitude values, along with a corresponding id that is associated with every row. when i am using the query SELECT latitu

Solution 1:

Recreating the scenario...

CREATE TABLE tasks 
    (
     lat integer, 
     lng integer, 
     id  integer
    );

INSERT INTO  tasks (lat, lng, id) VALUES (12, 34, 123);
INSERT INTO  tasks (lat, lng, id) VALUES (12, 34, 143);
INSERT INTO  tasks (lat, lng, id) VALUES (12, 35, 147);
INSERT INTO  tasks (lat, lng, id) VALUES (11, 35, 412);

And selecting with the query specified (note column names match table in data example)

SELECT lat, lng, COUNT(*) count FROM tasks
GROUP BY lat, lng HAVING count > 1

Gives the following:

lat   lng   count
12    34    2

...which is consistent with what you expect, except for "and for others i should get count as 1". To address that, and get all rows, remove the HAVING count > 1, yielding

lat   lng   count
11    35    1
12    34    2
12    35    1

If you're having a problem with the execution of the SQL using SQLiteDatabase.query(), then post a code sample with the output (or failure) so that the problem can be diagnosed.

Fiddling with this query

As for code in Android, here's an example that works using rawQuery. This seems to support the HAVING clause.

SQLiteDatabase db = SQLiteDatabase.create(null);

db = SQLiteDatabase.openDatabase("/data/data/com.mycompany.myapp/databases/myDB.db", null,    SQLiteDatabase.OPEN_READONLY, null);

Cursor cursor = db.rawQuery("SELECT lat, lng, COUNT(*) count  FROM tasks GROUP BY lat, lng  HAVING  count  > 1 ", new String [] {});

// Iterate through cursor
if(cursor.moveToFirst())
{
    do
    {
        Integer lat = cursor.getInt(0);
        Integer lng = cursor.getInt(1);
        Integer count = cursor.getInt(2);

        // Do something here with lat, lng, count

    }
    while(cursor.moveToNext());
}

There's no error handling, and I'd suggest using a DB Utilities class that extends SQLiteOpenHelper, but this should get you going.

Solution 2:

It looks like a problem in your SQL. When you create an Alias for a column, you probably should use the "AS" keyword.

I think your SQL statement should be:

SELECT latitude, longitude, COUNT(*) AS count FROM tasks GROUP BY latitude, longitude HAVING count > 1

or

SELECT latitude, longitude, COUNT(*) FROM tasks GROUP BY latitude, longitude HAVING COUNT(*) > 1