average population per city of each state

Asked

Viewed 52 times

0

Good afternoon, here I am stuck in more question of a job, need to make a query where it returns the average population per city of each state, I managed to perform some researches but all gave absurdly large numbers that I will show below.

SELECT e.nome,  AVG(m.populacao) pop     FROM municipio m     INNER JOIN estado e     ON e.codestado = m.codestado     GROUP BY e.nome     ORDER BY pop ASC     

database postgresql

  • Municipality already has the aggregate population ?

  • In these cases make it open to a state and check out.

  • I’m sorry, but you shouldn’t ask other people to do your job for you. Try to work hard to do it. If it’s a question, instructions have been given to allow you to do so.

  • @Motta , the municipality table relates to the state table through CODESTADO, and the regional table relates to state through CODREGIAO. I imagine that I have to do the sum of the population of the states and then do the media, I’ve tried several ways but none of them seemed to work out right. If you can tell me which command I should use to do it, I started studying comics now, so my knowledge is just the

  • I’m not asking to do for me BTW, there were about 18 questions in this job, I answered all the others alone, as the colleague is saying I’m asking to do for me.

  • Your sql seems correct , tried to make a case of a state ? Take a small , Sergipe or Acre and make an analysis to test.

  • SELECT e.name, m.name , m.populacao pop FROM municipio m INNER JOIN status e ON e.codestado = m.codestado andve.codestado = 'AC'

  • @Motta was seeing here that the result may be right but has many decimals, and I had not seen it, so I was thinking it was wrong,but observing now may be right, for example the Amazon is giving '51966.774193548387'

  • @Motta really think I was right, I used ROUND to round to two decimal places and apparently I was right

Show 4 more comments

1 answer

2


Your query is correct, your testing methodology is wrong:

I even managed to run some searches but all gave numbers absurdly large

What is big? What is small? What was the expected return value? The fact is that without a reference there is no way to determine whether your query is right or wrong.

Since you did not specify the structure of the tables estado and municipio, I’m going to rely on your query and assume that its table structure is something like:

CREATE TABLE estado
(
  codestado INTEGER PRIMARY KEY,
  uf CHARACTER VARYING(2),
  nome TEXT
);

CREATE TABLE municipio
(
  codmunicipio INTEGER PRIMARY KEY,
  codestado INTEGER REFERENCES estado (codestado), 
  nome TEXT,
  populacao INTEGER
);

Now, let’s insert some data into the created tables:

INSERT INTO estado (codestado, uf, nome) VALUES
( 1, 'PI', 'Piauí' ),
( 2, 'MA', 'Maranhão' ),
( 3, 'CE', 'Ceará' );

INSERT INTO municipio ( codmunicipio, codestado, nome, populacao ) VALUES
( 1, 1, 'Teresina', 864845 ),
( 2, 1, 'Parnaíba', 153078 ),
( 3, 1, 'Picos', 78222 ),
( 4, 2, 'São Luís', 1101884 ),
( 5, 2, 'Imperatriz', 258682 ),
( 6, 2, 'São José de Ribamar', 177687 ),
( 7, 3, 'Fortaleza', 2669342 ),
( 8, 3, 'Caucaia', 362400 ),
( 9, 3, 'Juazeiro do Norte', 274207 );

First, let’s make a simple query in the table of municipios to determine the value of the average population per city of a given federation state that we must wait in our tests, divide to conquer, come on:

--- Média populacional apenas das cidades do estado do Piauí
SELECT AVG(populacao) FROM municipio WHERE codestado = 1;

Exit:

|               avg |
|-------------------|
| 365381.6666666667 |

I do not know if this value is large or small, but it is a good reference to check whether darlings more complex are functioning as expected.

Now let’s go to the monster:

SELECT
  e.codestado,
  e.nome,
  AVG(m.populacao) pop
FROM
   estado e 
INNER JOIN
  municipio m ON (e.codestado = m.codestado)
GROUP BY
  e.codestado
ORDER BY
  pop ASC;

Exit:

| codestado | uf |     nome |               pop |
|-----------|----|----------|-------------------|
|         1 | PI |    Piauí | 365381.6666666667 |
|         2 | MA | Maranhão |            512751 |
|         3 | CE |    Ceará |           1101983 |

Note that the value of the expected population average for the state of Piauí corresponds to the expected value, demonstrating that INNER JOIN and your GROUP BY are functioning properly.

See all this running on SQL Fiddle

  • I would only do the analytic listing the state cities and calculating the average.

Browser other questions tagged database postgresql

You are not signed in. Login or sign up in order to post.