Select rows where ID is repeated, but value in another column is different

Question

I have a table like this:

What I would like to be able to do is return the IDs and how many different types of fruit the ID is associated with other than 1 like so:

Can someone help me out? I don't think it should be that difficult, but I haven't had much luck.

Thanks!

Answer 1

I think you want the having clause and count(distinct):

select id, count(distinct fruit) as numfruit
from t
group by id
having count(distinct fruit) > 1;

Answer 2

Here you go:

select id, count (id)
from yourtable
group by id;

Demo