Re: HELP! Having Count Question

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

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB