Database query in Postgresql

Question

I am trying to make a query in Postgre with the following query:

SELECT t3.desc_serv,
       t3.nm_serv,
       t1.nm_usu,
       count(t2.id_atend) TOTAL_ATENDIMENTO
FROM usuarios t1
INNER JOIN historico_atendimentos t2 ON t1.id_usu = t2.id_usu
INNER JOIN servicos t3 ON t2.id_serv = t3.id_serv
WHERE t2.dt_fim::text LIKE '%2013-10%'
GROUP BY t1.nm_usu, t3.desc_serv, t3.nm_serv, t3.id_serv, t1.id_usu

In Mysql this same query was only doing so:

SELECT t3.desc_serv,
       t3.nm_serv,
       t1.nm_usu,
       count(t2.id_atend) TOTAL_ATENDIMENTO
FROM usuarios t1
INNER JOIN historico_atendimentos t2 ON t1.id_usu = t2.id_usu
INNER JOIN servicos t3 ON t2.id_serv = t3.id_serv
WHERE t2.dt_fim LIKE '%2012-06%'
GROUP BY t3.id_serv, t1.id_usu

I don’t understand why but in Postgre to work "right" I need to leave the group by as it is, but if I leave the same as Mysql that is how accurate generates error, stating that I need to add the other fields of select in the group.

ERROR: column "T3.desc_serv" should appear in the GROUP BY clause or be used in an aggregation functionLINE 1: SELECT T3.desc_serv,

Answer 1

The GROUP BY mysql does not adhere to the sql standard, meaning it offers greater flexibility and it is not necessary to specify all columns of the from list in the group by how the documentation shows. mysql - group by

Mysql extends the use of GROUP BY so that the select list can refer to nonaggregated Columns not named in the GROUP BY clause

Mysql extends the use of GROUP BY this means that SELECT can reference non-aggregated non-list columns in the GROUP BY clause

Answer 2

This is because the resolution of the GROUP BY in the Mysql is not orthogonal. All columns that are included in the selection where there is an aggregation operation should be indicated for the grouping.

This is a peculiar behavior of Mysql that is discussed here (text in English). However, the standard norm of any and all databases is exactly the behavior of Postgres.

Answer 3

SELECT t3.desc_serv,
       t3.nm_serv,
       t1.nm_usu,
       count(t2.id_atend) TOTAL_ATENDIMENTO
FROM usuarios t1
INNER JOIN historico_atendimentos t2 ON t1.id_usu = t2.id_usu
INNER JOIN servicos t3 ON t2.id_serv = t3.id_serv
WHERE t2.dt_fim::text LIKE '%2013-10%'
GROUP BY t3.desc_serv,
         t3.nm_serv,
         t1.nm_usu

GROUP by must repeat the select line