-
count on table
have this table OCC
COD.............NAME
001.............TOM
001.............SAM
002.............CAT
003.............DOG
003.............SAM
003.............TOM
004.............JACK
005.............TOM
005.............SAM
........................
........................
........................
........................
I'd like to get just cod with more name:
I tried this
SELECT COUNT(NAME) TOT, COD
FROM OCC
GROUP BY COD
HAVING COUNT(NAME) > 1
OK, I get this:
TOT..........COD
2............001
3............003
2............005
but I'd like to get also the records.
For example I want this (only the records with more name):
COD.............NAME
001.............TOM
001.............SAM
003.............DOG
003.............SAM
003.............TOM
005.............TOM
005.............SAM
How can I write this query??
Thanks
-
inline view
select
s.tot, o.cod, o.name
from
occ o,
(select count(name) tot, cod
from occ
group by cod
having count(name) > 1) s
where
o.cod = s.cod
-
Bet this is faster ...
Code:
Select
cod,
name
From
(
Select
cod,
name,
Count(*) Over (Partition By cod)
row_per_cod
From
OCC
)
Where
row_per_code > 1
...or...
Code:
Select
cod,
name
From
(
Select
cod,
name,
Count(distinct name) Over (Partition By cod)
name_per_cod
From
OCC
)
Where
name_per_code > 1
-
slim, you are the $h!t. Awesome code.
Can you use analytical to improve on this:
http://www.dbasupport.com/forums/sho...threadid=42387
I know you can.
Last edited by ddrozdov; 04-19-2004 at 10:39 AM.
-
Yes.
-
You're my idol.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|