CODEWITHSUNDEEP
mysql
Mark Prince Lawrence Decinal
Mark Prince Lawrence Decinal

Reputation: 35

mysql counting all of each dupplicate in a table

Good Day I'm cuurently having problem I don't know why I cant do it with count

I have a column say...

colx 
aaa  
aaa
aaa
aaa
bbb
bbb
bbb
bbb
ccc
ccc
eee

I want the output to be 

Result No_of_Repeats
aaa    4
bbb    4
ccc    2
eee    1

Upvotes: 0

Views: 60

Answers (4)

Smita Ahinave
Smita Ahinave

Reputation: 1888

Simple Query...

select colx as Result,count(colx) as No_of_Repeats from tbl group by colx order by No_of_Repeats desc;

Upvotes: 0

Asad
Asad

Reputation: 3160

try this 

 select colx as result,count(colx) as No_of_Repeats from table1 group by colx;

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 181027

You can use a simple COUNT with GROUP BY to get the count by group.

SELECT colx result, COUNT(1) No_of_Repeats 
FROM table1
GROUP BY colx
ORDER BY COUNT(1) DESC

A simple SQLfiddle to test with.

Upvotes: 4

Pathik Vejani
Pathik Vejani

Reputation: 4501

You have to take main query in temp and then order by the count. Like this:

$q = "SELECT temp.* FROM (SELECT colx as Result, COUNT(colx) as No_of_Repeats FROM tbl_name GROUP BY colx) as temp ORDER BY temp.No_of_Repeats DESC";

Upvotes: 0

Related Questions