Question:
I'm trying to run a Postgre query 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 doing just like this:
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 it the same as in MySQL, it generates an error, stating that I need to add the other select fields in the group.
ERROR: Column "t3.desc_serv" must appear in GROUP BY clause or be used in a LINE 1 aggregate function: SELECT t3.desc_serv,
Answer:
The GROUP BY of mysql does not strictly follow the sql standard, that is, it offers greater flexibility, not being necessary to specify all columns from list in the group by as shown in the documentation. 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 refer to non-aggregated columns not lists in the GROUP BY clause