Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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>...Received on Mon Dec 16 2002 - 14:58:02 CST
>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,