Re: group by count

Hi,

With aggregate functions like COUNT you must you HAVING instead of WHERE, for example:

 select employeeid, count(employeeid)from employee   group by employeeid
 having count(employeeid) >0

Hope this helps,

Steve

In article <95c2sk$rl4$1_at_nnrp1.deja.com>,   fsconzo_at_my-deja.com wrote:
> 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/
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Feb 01 2001 - 11:20:21 CST