Re: HELP! Having Count Question

Pause for thought -

What would the output be if ALL the
duplicates were duplicates of the same
row ?

Answer: 1

You might follow up your query with
select id, count(*)
from
group by id
having count(*) > 2

to get better details of the more repetitious rows.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





oranewbie wrote in message
<947ef647.0212161246.2796a2e3_at_posting.google.com>...


>I'm running counts against a table with 15,467,245 rows.

>

>When I run a count on a table using distinct(id) I get the following

>results:

>

>select count(distinct(id)) from emp;

>------------------------------------

>15,254,678

>

>15,467,245 - 15,254,678 = 312,567 duplicates (easy right)

>

>

>However, when I try to pull off the duplicates with the having

>count(*). I get a different set of numbers:

>

>select count(*) from (select (account_id from emp having count(*) > 1

>group by

>account_id);

>-------------------------------------

>302,831 duplicates

>

>So, the having count(*) clause gives me 302,831 duplicates and

>distinct count gives me 312,567 duplicates.  This doesn't make since

>to me.  There aren't any nulls in the id either.  Any guru's have any

>suggestions?

>

>thanks for your time,