group by count

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 - 10:29:22 CST