PostgreSQL database query

Question

PostgreSQL database query

Navigation

#1 by (4 votes)
#2 by (2 votes)
#3 by (2 votes)

3

I'm trying to query 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 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 do not understand why but in Postgre to work "right" I need to leave group by as it is, but if I leave the same as MySQL, that's how I need to generate error, saying that I need to add the other select fields in the group. p>

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

sql postgresql

asked by anonymous 05.05.2014 / 17:11

3 answers

Should I use symbols in hashes and parameters to optimize memory usage in Ruby? ATM simulator

score 4 · Answer 1

The% mysql% does not follow the sql pattern, ie it offers greater flexibility, so you do not have to specify all the columns of GROUP BY in from list as 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 refer to non-aggregated columns not listed in the GROUP BY clause

score 2 · Answer 2

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 should repeat the select line

score 2 · Answer 3

This is because the resolution of GROUP BY in MySQL is not orthogonal. All columns that are included in the selection where there is an aggregate operation must be indicated for the collation.

This is a peculiar behavior of MySQL that is discussed here (text in English). However, the default rule for any database is exactly the behavior of Postgres .