Home » SQL & PL/SQL » SQL & PL/SQL » Finding IDs which have different CODE
Finding IDs which have different CODE [message #218576] Thu, 08 February 2007 14:01 Go to next message
pradkuamr
Messages: 29
Registered: November 2006
Junior Member
Lets say I am having a table as follows

ID CODE
1 AAA
2 BBB
3 CCC
4 DDD
3 CCC
2 BBB
1 AAA
2 FFF
2 GGG

I would likE the output to be as

ID CODE NUM_OF_OCCURENCES
1 AAA 2
2 BBB 2
2 FFF 1
2 GGG 1
3 CCC 2
4 DDD 1


I am trying the folowing SQL:

Select ID,CODE, COUNT(CODE) AS NUM_OF_OCCURENCES
FROM TABLE
GROUP BY ID,CODE
HAVING COUNT(CODE)>1
Re: Finding IDs which have different CODE [message #218578 is a reply to message #218576] Thu, 08 February 2007 14:39 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Use >=
Select ID,CODE, COUNT(CODE) AS NUM_OF_OCCURENCES
FROM TABLE
GROUP BY ID,CODE
HAVING COUNT(CODE)>=1

or > 0
Select ID,CODE, COUNT(CODE) AS NUM_OF_OCCURENCES
FROM TABLE
GROUP BY ID,CODE
HAVING COUNT(CODE)>0

[Updated on: Thu, 08 February 2007 14:42]

Report message to a moderator

Re: Finding IDs which have different CODE [message #218612 is a reply to message #218578] Fri, 09 February 2007 00:09 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
..or simply remove the having-clause?
Previous Topic: SQL%ROWCOUNT reporting 1 even though there is no records affected
Next Topic: about using fm ???
Goto Forum:
  


Current Time: Sun Dec 11 07:57:30 CST 2016

Total time taken to generate the page: 0.07478 seconds