Android SQLiteDatabase.query() GROUP BY / HAVING with more than one column not working

Question

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 latitude, longitude, COUNT(*) count
FROM tasks
GROUP BY latitude, longitude
HAVING count > 1

It is giving me group by latitude only and not longitude. how can I achieve this? I want to get the count of tasks with unique lat long values only. is there any way to achieve this using Android SQLite? For example, with this data in the table...

lat   lng   id
12    34    123
12    34    143
12    35    147
11    35    412

... for 12 lat 34 lng I must get 123, 143 i.e. count as 2 and for others I should get count as 1

Answer 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.

Answer 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