MySQL query problems

Question

MySQL query problems

Navigation

#1 by (0 votes)

2

I am having difficulty querying between 4 tables, being them. States > Municipalities > Deaths > Population The query is returning the value of the population with a higher than true number ... Can anyone help me with this query?

select estados.nome, sum(obitos.quantidade) as Obitos,
        sum(populacao.quantidade) as Pupolação

    from estados
    inner join municipios on (estados.idEstado = municipios.idEstado)
    inner join obitos on (municipios.idMunicipio = obitos.idMunicipio)
    inner join populacao on (municipios.idMunicipio = populacao.idMunicipio)
    where obitos.data between '2008-01-01' and '2008-12-01'
    and populacao.data = '2008-01-01' 
    GROUP BY estados.nome;

In this consultation I will add all populations of my municipalities and return the total by states, along with the number of deaths in the states. Here's the ER model:

mysql sql-select group-by mysql-workbench

asked by anonymous 05.10.2016 / 21:43

1 answer

Add the same column by different criteria? Validate Fields and Make Form Send

score 0 · Answer 1

Issue resolved:

SELECT sum(populacao.quantidade),
    estados.nome, 
    (SELECT sum(internacao.quantidade)
  from estados as est
  inner join municipios as mun on (est.idEstado = mun.idEstado)
  inner join internacao on (mun.idMunicipio = internacao.idMunicipio)
  where internacao.data between '2008-01-01' and '2008-12-01'
  and est.nome = estados.nome
  GROUP BY est.nome) as internacao
from estados
inner join municipios on estados.idEstado = municipios.idEstado
inner join populacao on municipios.idMunicipio = populacao.idMunicipio
where populacao.quantidade  
and populacao.data = '2008-01-01' GROUP BY estados.nome;