Mysql – Translating MySQL query to PostgreSQL

group byMySQLpostgresql

I have a query which works fine in MySQL, however when I try to recreate the same query in PostgreSQL (same DB structure) I just get a syntax error.

MySQL:

SELECT ticket.queue_id, queue.name, article.create_time,
COUNT(article.id)
FROM article
JOIN ticket
JOIN queue
ON article.ticket_id=ticket.id
and ticket.queue_id=queue.id
GROUP BY ticket.queue_id

Postgres:

SELECT ticket.queue_id, queue.name, article.create_time,
sum(article.id) FROM article
JOIN ticket
JOIN queue
ON article.ticket_id=ticket.id
AND ticket.queue_id=queue.id 
GROUP BY ticket.queue_id;

The error:

ERROR:  syntax error at or near "GROUP"
LINE 7: GROUP BY ticket.queue_id;
         ^

I'm quite at a loss here, as I can't really pinpoint my error.
The same error appears when I leave out the 'sum' statement in the query.

Any help much appreciated.

Best Answer

You must group by all of the columns i the select clause:

GROUP BY ticket.queue_id, queue.name, article.create_time

or add aggregate functions such as MAX:

SELECT ticket.queue_id, MAX(queue.name), MAX(article.create_time),
       COUNT(article.id)
FROM article
JOIN ticket
    ON article.ticket_id=ticket.id
JOIN queue
    ON ticket.queue_id=queue.id
GROUP BY ticket.queue_id

I also moved the ON clause.

SQL92 required a full group by, SQL99 loosened this and requires that all columns in the select clause should be functionally dependent of the group by clause. AFAIK, all vendors kept the stricter SQL92 definition.

You can make MySQL more compliant with other vendors by adding ONLY_FULL_GROUP_BY to @@sql_mode. By default MySQL will not complain if you "under specify" the group by, instead it will give you a random row. Example:

create table t (x int, y int, z int);
insert into t (x,y,z) values (1,1,1),(1,2,1);

select x,y,count(*) from t group by x;
+------+------+----------+
| x    | y    | count(*) |
+------+------+----------+
|    1 |    1 |        2 |
+------+------+----------+
1 row in set (0.00 sec)

set @@sql_mode = 'ONLY_FULL_GROUP_BY'

select x,y,count(*) from t group by x;
ERROR 1055 (42000): 'mysql.t.y' isn't in GROUP BY

There is also a bunch of other settings (PIPES_AS_CONCAT for example) that can be added to @@sql_info to make MySQL more compliant.

Related Question