23

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?

Marius Burz
  • 4,555
  • 2
  • 18
  • 28
cimnine
  • 3,987
  • 4
  • 33
  • 49

1 Answers1

55

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;
APC
  • 144,005
  • 19
  • 170
  • 281
Donnie
  • 45,732
  • 10
  • 64
  • 86
  • 3
    Isn't there a `group by` missing? `select fk, count(*) from table group by fk having count(*) > 1` – Dirk Nov 26 '09 at 16:52
  • @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`. – cimnine Nov 26 '09 at 16:55
  • 1
    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). – Jeffrey Kemp Nov 27 '09 at 03:56