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

Question

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

Navigation

#1 by (0 votes)

0

Hello, I have the following problem: I need to display the locations, the number of submissions for each one (that is, how many times the name of the locality appears in the column) and the most common disease in each locality , the name of the disease that is most frequently repeated in that specific locality).

FOR EXAMPLE:

locality: Croatian 1
number of submissions: 4
Most common disease: Conjutivite

I have these two query's but they do not 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 querry sql

php mysql sql

asked by anonymous 24.05.2018 / 16:18

1 answer

Redirect if the basket is empty | Woocommerce 3+ Select data from three SQL tables

score 0 · Accepted Answer

My suggestion is to create a VIEW that totals the amount based on the location and the illness:

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

Then make the selection of the largest 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)