ctcgag_at_hotmail.com wrote in message news:<20021219145749.353$LV_at_newsreader.com>...
> kellywh_at_hotmail.com (oranewbie) wrote:
> > 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)
>
> Are you sure there are no triplicates or higher replicates?
>
> >
> > 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
>
> That query is hosed. 4 opening parenthesis, only 3 closing ones.
> It's not clear if the 'group by' and 'having' are supposed to be on the
> same query level but in the wrong order or on different levels.
> Also, is the essential column here account_id or just id?
>
> Try this:
> select redundancy, count(*) from
> ( select count(*) as redundancy from emp group by account_id)
>
> Xho
Thanks everyone!!
I was able to run Xho's query which made more since in why I was
seeing the difference in counts using the distinct vs having count.
Thanks to everyone who offered a comment.
Received on Mon Jan 06 2003 - 14:08:32 CST
