Which country had the largest total population each year

Asked

Viewed 59 times

-1

I created this table:

CREATE TABLE `Populacao` (
  `ano` int(11) NOT NULL,
  `pais` varchar(2) NOT NULL,
  `popM` varchar(10) DEFAULT NULL,
  `popF` varchar(10) DEFAULT NULL,
);

Where popM is the male population and popF is the female population.

I’m trying to find out (query) which country had the largest total population in each year.

Nota 1: População total = popF + popM
Nota 2: popM e popF são campos 'varchar'
mysql sql query select
  • 2

    Which version of Mysql are you using? There is the possibility to change popM and popF columns to integer or some numeric type?

  • What values your system is sending to popM and popF columns ?

  • Mysql 5. No, I want to keep popM and popF varchar.

  • Send "83456765", for example. Numerical values, but in varchar.

1 answer

1


Make a subquery that manages the ratio of the largest populations for each year:

SELECT ano, max(cast(coalesce(popM, '0') AS unsigned)+cast(coalesce(popF, '0') AS unsigned)) AS maxpop
FROM Populacao
GROUP BY ano;

Possession of this relationship determine which parents(s) has that population that year:

SELECT Populacao.ano, Populacao.pais, x.maxpop FROM Populacao
INNER JOIN (SELECT ano, max(cast(coalesce(popM, '0') AS unsigned)+cast(coalesce(popF, '0') AS unsigned)) AS maxpop
            FROM Populacao
            GROUP BY ano) x
ON (Populacao.ano =x.ano AND (cast(coalesce(Populacao.popM, '0') AS unsigned)+cast(coalesce(Populacao.popF, '0') AS unsigned)) = x.maxpop);

Corrected.

Browser other questions tagged mysql sql query select

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