Get the record name in the most repeating column

Question

I need to display the record that repeats most in the column tipo_doenca from the column localidade, that is to exhibit the disease that is in the largest quantity in that locality.

Table name: Tb_grafico

Answer 1

You can simply group and sort to see which one repeats the most

select tipo_doenca, count(tipo_doenca)
  from tb_grafico
 where localidade = 'Centro'
 group by tipo_doenca
 order by count(tipo_doenca) desc

EDIT as noted by @sorack, if you want to see all localities, you can add the locality in the selectand in the group by. Also need to remove from where and it would be interesting to include in order by for easy viewing:

select localidade, tipo_doenca, count(tipo_doenca)
  from tb_grafico
 group by localidade, tipo_doenca
 order by localidade, count(tipo_doenca) desc

Answer 2

What else repeats itself, generally, it is so only one, therefore a limit goes well!

Example for locality bangu

SELECT tipo_doenca, COUNT(tipo_doenca) FROM TB_grafico
where localidade='bangu'
GROUP BY tipo_doenca
ORDER BY COUNT(tipo_doenca) DESC
LIMIT 1;

Test table

Upshot

At the request of @Sorack example in sqlfiddle.com

Answer 3

You can create a VIEW which counts occurrences by locality:

CREATE VIEW vw_ocorrencias AS (
  SELECT COUNT(1) AS ocorrencias,
         tg.localidade,
         tg.tipo_doenca
    FROM TB_grafico tg
   GROUP BY tg.localidade,
            tg.tipo_doenca
);

After that select only the data from VIEW which have no divergent record with the larger amount:

SELECT vo.*
  FROM vw_ocorrencias vo
 WHERE NOT EXISTS(SELECT 1
                    FROM vw_ocorrencias vo2
                   WHERE vo2.localidade = vo.localidade
                     AND vo2.tipo_doenca <> vo.tipo_doenca
                     AND vo2.ocorrencias > vo.ocorrencias)