Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Question: Learning to Count(*)
In article <38199fb0.86484920_at_news.supernews.com>,
anon_at_anon.com (Alex Skolnick) wrote:
> I have been beating my head trying to figure out how to accomplish the
> following:
>
> I have 1 table that I want to retrieve all the accounts that have more
> than 3 emails. for examples
>
> ID: EMAIL:
> --- -----------
> 1 A_at_A.COM
> 2 A_at_A.COM
> 3 B_at_B.COM
> 4 C_at_C.COM
> 5 A_at_A.COM
> 6 A_at_A.COM
>
> I would like to see:
> ID: EMAIL:
> ---- -----------
> 1 A_at_A.COM
> 2 A_at_A.COM
> 5 A_at_A.COM
> 6 A_at_A.COM
>
> I am familar with Count(*) and Count(column_name), but How can I use
> it in the case of a where statment: In pseudo-code it would like
> this:
>
> select id, email from LIST where count(email) > 3
>
> Any help would be appreciated.
>
>
This should work:
SELECT * FROM YOUR_TABLE
WHERE EMAIL IN
(SELECT EMAIL
FROM YOUR_TABLE
GROUP BY EMAIL
HAVING COUNT(*) > 3);
Yar
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Oct 28 1999 - 18:09:07 CDT