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 · Answer

I have resolved the same problem using the below code:

String query = "SELECT violationDate, COUNT(*) as date " +
            "FROM challan " +
            "WHERE challanType = '" + type + "' GROUP BY violationDate";

Here violationDate and date are two columns of the result table. date column will return occurrence.

cr.getInt(1)

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

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

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