Re: group by count

select employee_id, count(*)
from employee
group by employee_id
having count(*) > 1

Usually there is an infrequent need to use temp tables. You might want to improve on your knowledge of SQL, as this is really a very basic topic.

Hth,

Sybrand Bakker, Oracle DBA

<fsconzo_at_my-deja.com> wrote in message news:95c2sk$rl4$1_at_nnrp1.deja.com...
> Greetings all:
>
> I haven't been using Oracle that long but thought something like this
> would be pretty simple to do:
>
> I want to select the count of rows that have the same value in a
> particular column but only where the count is greater than zero.
>
> Example:
>
> select count(*), employee_id from employee group by employee_id;
>
> This might return:
> COUNT(*) EMPLOYEE_ID
> ---------- -----------
> 1 1
> 2 4
> 1 5
> 1 6
> 1 7
> 1 8
>
> but my database actually returns hundreds of thousands of rows. I want
> to filter the results to only show rows where the count > 1.
>
> This doesn't work: select count(*), employee_id from employee group by
> employee_id where count(*) > 1;
>
> I thought a subselect with a name on the count column might work, but
> that won't work:
>
> select idcount, employee_id from (select count(*) "idcount",
> employee_id from employee group by employee_id) where idcount > 1;
>
> It returns an invalid column name error.
>
> Anyone have any ideas?
> Perhaps I could create a temp table to do this - but I don't know how
> to create temp tables in oracle 8i.
>
> Any help would be appreciated.
> Best Regards,
> Frank Sconzo
>
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Thu Feb 01 2001 - 11:34:06 CST