... where count(col) > 1

Question

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 (
    select fk, count(value) as cnt from table
) where cnt > 1;

...with no success.

Any ideas?

Solution

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;

Comments

[bayer]

Can you give the error messages?

[Dirk]

Isn't there a group by missing? select fk, count(*) from table group by fk having count(*) > 1

[cimnine]

@Dirk: Yes, Oracle complained about it. I added the group by fk to the end to make it work; I don't know if it matters if it is before or after the having.

[Jeffrey Kemp]

HAVING comes after GROUP BY because logically it is only evaluated after the grouping has been done (i.e. it cannot determine the count for any particular fk until after all rows for that fk have been retrieved and counted).