... where count(col) > 1

I have a table like this:

+-----+-----+-------+
| id  | fk  | value |
+-----+-----+-------+
| 0   | 1   | peter |
| 1   | 1   | josh  |
| 3   | 2   | marc  |
| ... | ... | ...   |

I'd like now to get all entries which have more than one value. The expected result would be:

+-----+-------+
| fk  | count |
+-----+-------+
| 1   | 2     |
| ... | ...   |

I tried to achieve that like this:

select fk, count(value) from table where count(value) > 1;

But Oracle didn't like it.

So I tried this...

select * from (
    selec开发者_如何转开发t fk, count(value) as cnt from table
) where cnt > 1;

...with no success.

Any ideas?

Use the having clause for comparing aggregates.

Also, you need to group by what you're aggregating against for the query to work correctly. The following is a start, but since you're missing a group by clause still it won't quite work. What exactly are you trying to count?

select fk, count(value) 
from table 
group by fk
having count(value) > 1;