CODEWITHSUNDEEP
mysql
Shamss Zaidan
Shamss Zaidan

Reputation: 635

MySQL: Retrieve Values and Counts For Each

How can I count the occurrence of the field/column in SQL?

Example dataset:

A
A
A
A
B
B
C

I want:

A  |  4
A  |  4
A  |  4
A  |  4
B  |  2
B  |  2
C  |  1

Is there anyway to do it without using GROUP BY? So far all answer I get my query retuns the following:

A  |  4
B  |  2
C  |  1

Upvotes: 0

Views: 61

Answers (2)

Bence Gajdán
Bence Gajdán

Reputation: 612

You could use a nested sub-select for this desired result set. If the example table name is my_table and the column called col1:

select col1,
    (select count(*) from my_table where col1 = t.col1) as Count
from my_table t;

Or if you want to remove the duplicates, use the distinct statement. It removes the duplicates of your result set.

select distinct col1,
    (select count(*) from my_table where col1 = t.col1) as Count
from my_table t;

Upvotes: 1

Komalpreet Singh
Komalpreet Singh

Reputation: 165

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: 2

Related Questions