0

Before, we were using mysql for our database but when we migrated to postgresql we encountered this problem.

SQLSTATE[42803]: Grouping error: 7 ERROR: column "table.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT COUNT(*) FROM (SELECT * FROM (SELECT * FROM "kv_firmw... ^ The SQL being executed was: SELECT COUNT(*) FROM (SELECT * FROM (SELECT * FROM "kv_firmware_release" ORDER BY "id" DESC) "table" GROUP BY "filename") "c"

I have this code in Yii2 search model.

    $subquery = new Query;   
    $subquery->select(['*']);
    $subquery->from('kv_firmware_release')->orderBy('id DESC');

    $query = new Query;
    $query->select(['*']);
    $query->from(['table' => $subquery])->groupBy('filename');

    $query->limit(5);

This is my first time to use Psql. It works fine before, but now it is giving me the error. How can I fix this in Yii2?

Gibs
  • 774
  • 9
  • 26
  • $query->select(['*'])->groupBy('filename') does not make sense in mysql either. Group by filename but return all the fields? How is that suppose to make sense in any DB? It works but your results will be more or less random. – Mihai P. Mar 11 '16 at 04:57

2 Answers2

0

This is simple, with mysql is optional put all the columns in field list and group by clause, in postgres and other databases you must put all columns.

So when you are working with aggregates put in fields list the columns that you will use, avoid unnecessary fields.

Mysql exemple

SELECT id, name, email FROM contact GROUP BY id

In Postgres

SELECT id, name, email FROM contact GROUP BY id, name, email
rray
  • 2,518
  • 1
  • 28
  • 38
  • thanks but I don't have an idea how I will do this in Yii2. Can you add more tips? – Gibs Mar 10 '16 at 14:21
  • @Gibs first change the `*` in `$query->select(['*']);` for the name of columns and put the same list in `->groupBy('filename');` like, `->groupBy('id, filename');` or `->groupBy(['id', 'filename']);` – rray Mar 10 '16 at 14:23
  • @Gibs [How to count and group by in yii2](http://stackoverflow.com/q/24389765/1342547) – rray Mar 10 '16 at 14:37
  • I am trying first this `select filename FROM (SELECT id, remarks, filename FROM "kv_firmware_release" ORDER BY "id" DESC) b group by (b.filename)` this worked but why does `select id, remarks, filename FROM (SELECT id, remarks, filename FROM "kv_firmware_release" ORDER BY "id" DESC) b group by (filename)` not work? I wanted to get id and remarks at the same time too. Is my syntax wrong? – Gibs Mar 10 '16 at 15:42
  • error is `SQL error: ERROR: column "b.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select id, remarks, filename FROM (SELECT id, remarks, filen... ^ In statement: select id, remarks, filename FROM (SELECT id, remarks, filename FROM "kv_firmware_release" ORDER BY "id" DESC) b group by (filename)` – Gibs Mar 10 '16 at 15:54
0

in PostgreSQL when use group by you must write all fields using in select or unique or primary key

For example

SELECT id, name, email FROM contact GROUP BY id, name, email

OR

SELECT id, name, email FROM contact GROUP BY id

wil work when column id is primary or unique key

sxn
  • 157
  • 1
  • 7