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

CREATETABLE tasks 
    (
     lat integer, 
     lng integer, 
     id  integer
    );

INSERTINTO  tasks (lat, lng, id) VALUES (12, 34, 123);
INSERTINTO  tasks (lat, lng, id) VALUES (12, 34, 143);
INSERTINTO  tasks (lat, lng, id) VALUES (12, 35, 147);
INSERTINTO  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
GROUPBY 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.

SQLiteDatabasedb= SQLiteDatabase.create(null);

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

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

// Iterate through cursorif(cursor.moveToFirst())
{
    do
    {
        Integerlat= cursor.getInt(0);
        Integerlng= cursor.getInt(1);
        Integercount= 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 GROUPBY latitude, longitude HAVING count >1

or

SELECT latitude, longitude, COUNT(*) FROM tasks GROUPBY latitude, longitude HAVINGCOUNT(*) >1