CODEWITHSUNDEEP
mysqlcountuniqueduplicates
Ian
Ian

Reputation: 12241

Retrieve Unique Values and Counts For Each

Is there a simple way to retrieve a list of all unique values in a column, along with how many times that value appeared?

Example dataset:

A
A
A
B
B
C

... Would return:

A  |  3
B  |  2
C  |  1

Upvotes: 20

Views: 21389

Answers (2)

GoatRider
GoatRider

Reputation: 1213

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

Upvotes: 5

cdonner
cdonner

Reputation: 37668

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

Upvotes: 51

Related Questions