Count the occurrences of DISTINCT values

Question

I am trying to find a MySQL query that will find DISTINCT values in a particular field  count the number of occurrences of that value and then order the results by the count   example db  id         name -----      ------ 1          Mark 2          Mike 3          Paul 4          Mike 5          Mike 6          John 7          Mark   expected result  name       count -----      ----- Mike       3 Mark       2 Paul       1 John       1

User · Accepted Answer

SELECT name COUNT    as count  FROM tablename  GROUP BY name  ORDER BY count DESC

User · Answer

What about something like this   SELECT   name    count    AS num FROM   your table GROUP BY   name ORDER BY   count      DESC   You are selecting the name and the number of times it appears  but grouping by name so each name is selected only once   Finally  you order by the number of times in DESCending order  to have the most frequently appearing users come first

User · Answer

I have resolved the same problem using the below code  String query    quot SELECT violationDate  COUNT    as date  quot                 quot FROM challan  quot                 quot WHERE challanType     quot    type    quot   GROUP BY violationDate quot         Here violationDate and date are two columns of the result table  date column will return occurrence  cr getInt 1

User · Answer

Just changed Amber s COUNT    to COUNT 1  for the better performance   SELECT name  COUNT 1  as count  FROM tablename  GROUP BY name  ORDER BY count DESC

[mysql] Count the occurrences of DISTINCT values

Examples related to mysql

Examples related to sql

Examples related to database

Examples related to distinct