How to show the number of MySQL replicates?

Question

How to show the number of MySQL replicates?

Navigation

#1 by (7 votes)

3

I have a bank where names and last names are registered, I can group the results as follows:

SELECT DISTINCT * FROM usuarios GROUP BY sobrenome

So if you have 80 records repeated for the last name "Jose" for example, obviously it groups and shows only 1 "Jose", but I want to names, I want to tell 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 ones, 5 most common surnames in the table . Is there a way to do this directly in MySQL or do I need to create some script in PHP?

php mysql

asked by anonymous 19.12.2015 / 15:02

1 answer

How to reference an external library? Error: Object reference not set to an instance of an object, how to solve?

score 7 · Accepted Answer

Do a SQL like this. Put limit 2 at the end to make it easier to mount the fiddle. In your case, use limit 5 .

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

SQL running on this Fiddle

Database mount code in Fiddle, putting here to register.

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");

UPDATE

Taking only the current date, considering that the column date is the date of the registration:

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