How to show the number of Mysql repetitions?

Question

I have a bank in which are registered names and surname, I can group the results as follows:

SELECT DISTINCT * FROM usuarios GROUP BY sobrenome

So if you have 80 records repeated of the surname "Joseph" for example, obviously it groups and shows only 1 "Joseph", but I want to not only group the names, I want to count which are the 5 most repeated surnames of the table, ie, even having more than 30 repeated surnames for example, I want to show only the 5 most repeated, ie the 5 most common table surnames. Is there any way to do this directly in Mysql or need to create some script in PHP?

Answer 1

Make an SQL this way. Put limit 2 at the end to make it easier to assemble the fiddle. In your case uses limit 5.

select n.sobrenome, count(*) from nomes n
group by n.sobrenome
order by count(*) desc
limit 2

SQL running on this Fiddle

Fiddle database mount code, put here to record.

create table nomes(
  id varchar(10),
  nome varchar(40),
  sobrenome varchar(40),
  endereco varchar(40));

  insert into nomes values(
    "1","ricardo","chaves","endereco ricardo");

      insert into nomes values(
    "2","daniel","chaves","endereco daniel");

      insert into nomes values(
    "3","maria","madalena","endereco maria");

      insert into nomes values(
    "4","felipe","geraldo","endereco geraldo");

      insert into nomes values(
    "5","jose","geraldo","endereco jose");

      insert into nomes values(
    "6","jose","madalena","endereco jose1");

    insert into nomes values(
    "6","Maria2","madalena","endereco maria2");

UPDATING

Taking only the current date, whereas the column date is the date of registration:

    select n.sobrenome, data, count(*) 
      from usuarios n 
     where n.date = (data atual)
  group by n.sobrenome order by count(*) 
desc limit 5