CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Registered User
Registered User

Reputation: 1564

Find a record which has multiple occurrences?

I've a table like this

Acode  Bcode

100     4
101     3
100     8
105     4
105     8
104     1
109     8
110     3
109     8

I would like to find out Which are Acode belongs to more than one Bcode like 100 belongs to 4 as well 8 and 105 belongs to 4 as well 8 so on.

Upvotes: 3

Views: 10330

Answers (3)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

SELECT Acode,Count(Acode)
FROM TABLE1
GROUP BY Acode 
HAVING Count(Acode) > 1

If you want to find out Which are Acode belongs to more than one Bcode then

Try this

SELECT T.ACode,T.BCode,S.Acode
FROM table1 T JOIN
(
  SELECT Acode,Count(Acode) As CCode
  FROM TABLE1
  GROUP BY Acode 
  HAVING Count(Acode) > 1
) As S ON S.ACode = T.ACode

FIDDLE DEMO

Output

ACODE   BCODE
100     4
100     8
105     4
105     8
109     8
109     8

Upvotes: 1

g2server
g2server

Reputation: 5367

try this

SELECT Acode, COUNT(Bcode) 
FROM TableName
GROUP BY Acode
HAVING COUNT(Bcode) > 1

Upvotes: 5

ngrashia
ngrashia

Reputation: 9894

Try this.. It should help. This will display both ACODE and BCODE

SELECT A.ACODE, A.BCODE
  FROM myTable A
 WHERE EXISTS (SELECT B.ACODE
                 FROM myTable B
                WHERE B.ACODE = A.ACODE
                  AND B.BCODE <> A.BCODE );

If you need only ACODE and COUNT, then this would help:

SELECT ACODE, COUNT(BCODE) 
FROM MYTABLE
GROUP BY ACODE
HAVING COUNT(BCODE) > 1

Upvotes: 0

Related Questions