Retrieve Unique Values and Counts For Each

SELECT id,COUNT(*) FROM file GROUP BY id

Use GROUP BY:

select value, count(*) from table group by value

Use HAVING to further reduce the results, e.g. only values that occur more than 3 times:

select value, count(*) from table group by value having count(*) > 3