Re: GROUP BY question

Jim Reynolds wrote:
>
> I have a fairly simple question about GROUP BY. I'm sure other people have
> had the same problem, but I couldn't find any related questions in a
> DejaNews search. Pardon if this is a FAQ, but...
>
> Assume a table that has three fields: name, ID, and e-mail address. ID is
> a sequence-- no two rows have the same ID#.
>
> How would I go about printing all the info(name, id, and e-mail) about
> all the people who share an e-mail address (the address is listed more
> than once)?
>
> select email, count(*)
> from table
> group by email
> having count(*) > 1
>
> That query gives me a listing of all the duplicate e-mail addresses.
> This is close, but I need the names and IDs too. The following query
> doesn't work, because the names and id's for a particular e-mail address
> are not the same. Hence, count(*) always equals 1.
>
> select name, id, email, count(*)
> from table
> group by name, id, email
> having count(*) > 1
>
> This query works, providing the inner select list isn't very large (it is,
> around 25,000 records).
>
> select name, id, email
> from table
> where email in
> (select email, count(*)
> from table
> group by email
> having count(*) > 1
> )
>
> What's an efficient, workable way of doing this? Using MINUS?
>
> Any suggestions would be appreciated.
>
> Thanks.
>
> Jim.

Hi,

try

select name, id, email
from table t,
(select email, count(*)
  from table
  group by email
  having count(*) > 1
 ) s
where t.email = s.email  

-- 
Regards

M.Gresz    :-)