Sql query bring repeated result

Asked

Viewed 202 times

2

How do I list only the sector column that has repeated data? in case, just show the sector 150

SELECT setor,count(quantidade) as quantidade, usuario 
FROM arquivo_coletor 
GROUP BY setor,usuario 
ORDER BY setor ASC

inserir a descrição da imagem aqui

  • Hello @Hugorutemberg. Should the result be the two sector 150 records? Or just one with the sector and the sum of the quantity? Or something else?

3 answers

1

According to https://forum.imasters.com.br/topic/384021-comando-contr%C3%A1rio-de-distinct/

Edit1: According to this other source http://www.mysqltutorial.org/mysql-find-duplicate-values/ the command below does exactly what you described.

select campo from tabela group by campo having count(*) >1

Edit2: Excuse me, probably the mistake was mine in not showing in practice how this command would be adapted to your case. I suppose it is so:

select setor,count(quantidade) as quantidade, usuario from arquivo coletor group by quantidade having count(quantidade) >1

I don’t know so much about SQL so there won’t be a more theoretical explanation, but I think it would look like this.

  • This way the result deletes the data in duplicity. I need the result to be just what I have in duplicity.

  • I’ll do another search here

  • 1

    No @Hugorutemberg, this query brings exactly what you need, namely the results with more than 1 records (duplicates, triplicates, etc).

  • @João Martins, devo esta fazendo confusão ;select setor,usuario from arquivo_coletor group by usuario having count(*) >1 Result looks like this; sector | user 136 | 001 150 |002

  • I refer to the second link, and try to adapt the source code to your problem.

0


If you wish to obtain only those records, with sum of quantity, whose key setor + usuario has duplicates, can use the following solution:

SELECT      setor
        ,   SUM(quantidade) AS quantidade
FROM        arquivo_coletor
GROUP BY    setor
HAVING      COUNT(distinct setor + usuario) > 1

The records grouped by setor and usuario:

SELECT      AC.setor
        ,   AC.usuario
        ,   SUM(ac.quantidade) AS quantidade
FROM        arquivo_coletor AC
INNER JOIN  (
                SELECT      setor
                        ,   SUM(quantidade) AS quantidade
                FROM        arquivo_coletor
                GROUP BY    setor
                HAVING      COUNT(distinct setor + usuario) > 1
            )               AC2 ON AC2.setor = AC.setor
GROUP BY    AC.setor
        ,   AC.usuario
  • still does not bring only duplicates

  • Something is not right on your table, for sure! You can set a scenario on SQL Fiddle?

  • Here you are http://sqlfiddle.com/#! 9/c52a39/1

  • SQL Fiddle is empty...

  • see now - http://sqlfiddle.com/#! 9/7e6a8a/1

  • I see your problem! Edited answer.

  • Perfect! Now it’s right.

  • Take advantage and give an UP in the answers you consider useful (including this) :)

Show 3 more comments

0

Give a select in all fields and group by them, after a having Count>1

  select setor,quantidade,usuario 
     from arquivo_coletor
    group by setor,quantidade,usuario 
    having count(*) >1
  • That way he brings it up that way. 

setor | quantidade | usuário
136 |1 |001
136 |2 |001
136 |4 |001

Gostaria de enxergar assim;
setor | quantidade | usuário
150 |5 |001
150 |5 |002

Browser other questions tagged

You are not signed in. Login or sign up in order to post.