Aggregate functions are not Alloowed in the GROUP BY clause(code 1)

Question

I'm having this following weird issue with Sqlite query in Android 4.4.2 version devices.Thanks in advance

The table structure is as below

CREATE TABLE responsemaster (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    formid integer,
    proposerid text,
    fieldid integer,
    response text,
    remarks text,
    listId integer,
    isMarked integer
)

and the query that giving error is

SELECT id as id, formid as formid, proposerid as proposerid, MIN(fieldid) fieldid, response as response, remarks as remarks, listId as listId, isMarked as isMarked
FROM responsemaster
WHERE formid = 40066 AND proposerid = '7ca6533a-c5f0-43e2-9980-83f9ae2c7370201802230113550000'
GROUP BY fieldid

The exception i'm getting is as follows

android.database.sqlite.SQLiteException: aggregate functions are not allowed in the GROUP BY clause (code 1):, 
while compiling: SELECT id as id, formid as formid, proposerid as proposerid,  **MIN(CAST(fieldid AS INTEGER)) AS fieldid**, response as response, remarks as remarks, listId as listId, isMarked as isMarked FROM responsemaster WHERE formid = 40066 AND proposerid = '7ca6533a-c5f0-43e2-9980-83f9ae2c7370201802230113550000'  GROUP BY fieldid

The above query will execute successfully in SQLite browser and in Greater than Android 4.4.2 version devices..Please can any one help me how i can able to fix that error?

Perhaps support for that was added in SQLite 3.8, as Android 4.4 and older [use older versions of SQLite](https://stackoverflow.com/a/4377116/115145). — CommonsWare, Feb 23 '18 at 12:13

Tomin B Azhakathu · Accepted Answer · 2018-02-23T12:34:51.147

0

try this query it may replace the aggregate function

SELECT id as id, formid as formid, proposerid as proposerid, fieldid as 
       fieldid, response as response, remarks as remarks, listId as listId, 
       isMarked as isMarked FROM responsemaster WHERE formid = 40066 AND proposerid = '7ca6533a-c5f0-43e2-9980-83f9ae2c7370201802230113550000' 
       GROUP BY fieldid HAVING fieldid = MIN(fieldid)

edited Feb 23 '18 at 12:34

answered Feb 23 '18 at 12:14

Tomin B Azhakathu

2,656
1
19
28

@Shiva Try the above query. That may help you to avoid aggregate function in group by clause. Add a Having Clause – Tomin B Azhakathu Feb 23 '18 at 12:20
Happy to here that .Happy Coding Bro. – Tomin B Azhakathu Feb 23 '18 at 12:34

score 0 · Answer 2 · answered Feb 23 '18 at 12:39

0

Use HAVING clause instead of WHERE with AGGREGATE Functions :) It will solve it

https://learn.microsoft.com/en-us/sql/ssms/visual-db-tools/use-having-and-where-clauses-in-the-same-query-visual-database-tools

answered Feb 23 '18 at 12:39

Zainab Jamil

143
10

Aggregate functions are not Alloowed in the GROUP BY clause(code 1)

2 Answers2