SQL group by returns duplicate POSTGRESQL

Question

SQL group by returns duplicate POSTGRESQL

Navigation

#1 by (1 votes)

0

My SQL query is returning multiple rows with the same id following:

SELECT distinct (c.nome), c.id, c.email,c.telefone1,c.telefone2,
  SUM(a.valor) AS "valorDaCompra",u.id AS  "idLoja",u.nome AS  "nomeLoja", 
  SUM(a.qtd_pecas) AS "qtdPecasCompradas", a.data_hora as "dataHora",
  c.data_hora as "dataCadastro" 
FROM cliente as c
INNER JOIN atendimento AS a 
  ON a.id_cliente = c.id INNER JOIN usuario AS u
  ON a.id_loja = u.id 
WHERE a.id_empresa= 843
  AND a.id_loja IN (2855) 
  AND a.venda = true 
GROUP BY a.data_hora, c.id,a.id_cliente,u.id,c.data_hora 
HAVING SUM(a.valor) >= 2 AND SUM(a.valor) <= 20000
ORDER BY c.id DESC

sql postgresql

asked by anonymous 01.12.2017 / 20:27

1 answer

Searching for existence of record before including a new one (Yii 2) Tree Transformation

score 1 · Answer 1

I do not know the structure of your tables, which makes the analysis a bit difficult, but check the following points:

Relationship a.id_loja = u.id. . Is a user a store? If the answer is no. Your relationship is wrong.

There are aggregations in your query SUM (a.value) and SUM (a.qtd_pecas) . The rule is that all other fields are informed in GROUP BY. Staying this way: GROUP BY c.name, c.id, c.email, c.telephone1, c.telephone2, u.id, u.name, time.data, time.data . In this case, destinct is not required.

It is likely that the customer name will appear several times if it has been purchased on different days and times (a.data_time) .