Count repetition amount of an item

Asked

Viewed 106 times

1

I have a table where were inserted several repeated items, I need to return all these items that have more than one register. So I made the following query:

SELECT
  COUNT(ID),
  CODIGO,
  DESCRICAO,
  UNIDADE
FROM TB_ITEM
GROUP BY
  CODIGO,
  DESCRICAO,
  UNIDADE
HAVING COUNT(ID) > 2

Current situation of the table:

ID | CODIGO | DESCRICAO | UNIDADE
1  | 0098   | Teste A   | UN
2  | 0098   | Teste A   | UN
3  | 0098   | Teste A   | UN
4  | 0098   | Teste A   | UN
5  | 0098   | Teste B   | CX
6  | 0098   | Teste B   | CX
7  | 0098   | Teste B   | CX
8  | 0098   | Teste B   | CX
9  | 0098   | Teste B   | CX
10 | 0098   | Teste B   | CX

Expected result

QTDE | CODIGO | DESCRICAO | UNIDADE
4    | 0098   | Teste A   | UN
6    | 0098   | Teste B   | CX

The actual table has thousands of items repeated, but I sampled my result and it seemed to give me a false result. Can anyone tell me if this is the right approach to the query?

sql sql-server query

  • In my view the query is correct, can filter a line and to be able to compare the difference between them and so sort the problem?

  • @Bulfaitelo I could not understand your doubt

  • 1

    You need to return records that have more than one record, correct? your HAVING COUNT(ID) > 2 is returning only records that have 3 up, is that right? If you put HAVING COUNT(ID) > 1 would not solve?

  • Your QUERYseems to be correct even because, if you add an item any other than the others, it will not come in the query. Or if you change the HAVING COUNT(ID) > 2for HAVING COUNT(ID) > 5, will see that will only bring the Teste B

  • @Adrianogomes What you want to know is how many different items (pair DESCRIPTION, UNIT) have the same value for CODE?

1 answer

1


If you want to have items that have more than one register then you will have to change the COUNT:

SELECT      COUNT(1) AS QTDE
        ,   CODIGO
        ,   DESCRICAO
        ,   UNIDADE
FROM        TB_ITEM
GROUP BY    CODIGO
        ,   DESCRICAO
        ,   UNIDADE
HAVING      COUNT(1) > 1

That’s probably why I gave him a false result.

Browser other questions tagged sql sql-server query

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