Display a record by quantity and the most repeated value of a column

Question

Hello, I have the following problem: I need to display the localities, the number of shipments of each one (ie how often the name of the locality appears in the column) and the most common disease of each locality (that is, the name of the most common disease in that particular locality).

FOR EXAMPLE:

• locality: Croatian¡ 1
• Number of submissions: 4
• most common disease: Conjunctivitis

---

I have these two query’s but they don’t work together, just one or the other

SELECT localidade, count(localidade) AS qt FROM tb_grafico group by localidade;

---

SELECT localidade, tipo_doenca, COUNT(tipo_doenca) as qtidade FROM tb_grafico
where localidade='Croatá 1'
GROUP BY tipo_doenca
ORDER BY COUNT(tipo_doenca) DESC
LIMIT 1;

preferably in a single sql querry

Answer 1

My suggestion is that you create a VIEW totalling the amount based on locality and disease:

CREATE VIEW vw_grafico AS
  SELECT localidade,
         tipo_doenca,
         COUNT(localidade) AS quantidade
    FROM tb_grafico
   GROUP BY localidade,
            tipo_doenca;

After that make the selection of the biggest disease in vw_grafico:

SELECT g.*,
       (SELECT SUM(g3.quantidade)
          FROM vw_grafico g3
         WHERE g3.localidade = g.localidade) AS total
  FROM vw_grafico g
 WHERE NOT EXISTS(SELECT 1
                    FROM vw_grafico g2
                   WHERE g2.localidade = g.localidade
                     AND g2.tipo_doenca <> g.tipo_doenca
                     AND g2.quantidade > g.quantidade)